1、JDBC是什么？古人云，温故而知新。在忙碌的现在，大学同学们都在急急忙忙的找工作，我感到十分焦虑，于是乎，本人在假期便复习了JavaWeb方面的知识。此时此刻，记录下正在电脑前，奋笔疾书的自己。
JDBC是提供Java程序员，操作数据库比如MySQL的一系列规范准则。这些准则规范，由数据库厂商来对这些对象的方法，进行因地制宜的实现。也就是程序界中所言，面向接口编程。
他抽取定义了如下三个对象，描述了Java程序使用MySQL数据库的过程：1.获取连接 2.使用预编译陈述对象执行Sql 3.针对查询数据库得到的列封装到结果集对象中。
import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet;2、使用JDBC操作MySQL数据库
因为，每一次数据库操作都需要获取连接，释放连接。对于重复做的事情。我们可将其抽取成一个工具类，对外暴露方法，让代码看着更为简洁。
2.1、JDBCUtilspackage com.wnx.mall.tiny.fruit.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtils {
public static Connection getConnection() throws SQLException,
ClassNotFoundException {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/javaweb";
String username = "root";
String password = "root";
Connection conn = DriverManager.getConnection(url, username,password);
return conn;
}
public static void release(Statement stmt, Connection conn) {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
public static void release(ResultSet rs, Statement stmt,
Connection conn){
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
release(stmt, conn);
}
}
2.2、编写操作数据库的对象
一般来说，操作数据库的对象，我们以XXXDAO,来描述，由该对象，来操作数据库，和数据库打交道。
ORM对象关系映射指明了：对于数据库的表必须存在一个JavaBean与之对应开来。
2.2.1、Model一个标准的JavaBean,我们要求，他必须有一个无参构造器，满参构造器。属性名以及属性的类型与MySQL数据库表字段统一。
package com.wnx.mall.tiny.fruit.domain;
public class Fruit {
private Integer fid ;
private String fname ;
private Integer price ;
private Integer fcount ;
private String remark ;
public Fruit(){}
public Fruit(Integer fid, String fname, Integer price, Integer fcount, String remark) {
this.fid = fid;
this.fname = fname;
this.price = price;
this.fcount = fcount;
this.remark = remark;
}
public Integer getFid() {
return fid;
}
public void setFid(Integer fid) {
this.fid = fid;
}
public String getFname() {
return fname;
}
public void setFname(String fname) {
this.fname = fname;
}
public Integer getPrice() {
return price;
}
public void setPrice(Integer price) {
this.price = price;
}
public Integer getFcount() {
return fcount;
}
public void setFcount(Integer fcount) {
this.fcount = fcount;
}
public String getRemark() {
return remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
@Override
public String toString() {
return fid + "tt" + fname + "tt" + price +"tt" + fcount +"tt" + remark ;
}
}
2.2.2、FruitDAO
package com.wnx.mall.tiny.fruit.dao;
import com.wnx.mall.tiny.fruit.domain.Fruit;
import java.util.List;
public interface FruitDAO {
List getFruitList();
boolean addFruit(Fruit fruit);
boolean updateFruit(Fruit fruit);
Fruit getFruitByFname(String fname);
boolean delFruit(String fname);
boolean delete(int fid);
Fruit findById(int fid);
}
2.2.3、FruitDAOImpl
package com.wnx.mall.tiny.fruit.dao.impl;
import com.wnx.mall.tiny.fruit.dao.FruitDAO;
import com.wnx.mall.tiny.fruit.domain.Fruit;
import com.wnx.mall.tiny.fruit.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class FruitDAOImpl implements FruitDAO {
@Override
public List getFruitList() {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
ArrayList list = new ArrayList<>();
try {
conn = JDBCUtils.getConnection();
String sql = "select * from t_fruit";
stmt = conn.prepareStatement(sql);
rs = stmt.executeQuery();
while (rs.next()) {
Fruit fruit = new Fruit();
fruit.setFid(rs.getInt("fid"));
fruit.setFname(rs.getString("fname"));
fruit.setPrice(rs.getInt("price"));
fruit.setFcount(rs.getInt("fcount"));
fruit.setRemark(rs.getString("remark"));
list.add(fruit);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
return null;
}
@Override
public boolean addFruit(Fruit fruit) {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
String sql = "insert into t_fruit values(0,?,?,?,?)";
stmt = conn.prepareStatement(sql);
stmt.setString(1, fruit.getFname());
stmt.setInt(2, fruit.getPrice());
stmt.setInt(3, fruit.getFcount());
stmt.setString(4, fruit.getRemark());
int count = stmt.executeUpdate();
if (count > 0) { return true; }
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.release(rs, stmt, conn);
}
return false;
}
@Override
public boolean updateFruit(Fruit fruit) {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
String sql = "update t_fruit set fcount = ? where fid = ? " ;
stmt = conn.prepareStatement(sql);
stmt.setInt(1,fruit.getFcount());
stmt.setInt(2,fruit.getFid());
int count = stmt.executeUpdate();
if (count > 0) { return true; }
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.release(rs, stmt, conn);
}
return false;
}
@Override
public Fruit getFruitByFname(String fname) {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
String sql = "select * from t_fruit where fname like ? ";
stmt = conn.prepareStatement(sql);
stmt.setString(1,fname);
rs = stmt.executeQuery();
while (rs.next()) {
Fruit fruit = new Fruit();
fruit.setFid(rs.getInt("fid"));
fruit.setFname(rs.getString("fname"));
fruit.setPrice(rs.getInt("price"));
fruit.setFcount(rs.getInt("fcount"));
fruit.setRemark(rs.getString("remark"));
return fruit;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
return null;
}
@Override
public boolean delFruit(String fname) {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
String sql = "delete from t_fruit where fname like ? " ;
stmt = conn.prepareStatement(sql);
stmt.setString(1,fname);
int count = stmt.executeUpdate();
if (count > 0) { return true; }
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.release(rs, stmt, conn);
}
return false;
}
@Override
public boolean delete(int fid) {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
String sql = "delete from t_fruit where fid = ? " ;
stmt = conn.prepareStatement(sql);
stmt.setInt(1,fid);
int count = stmt.executeUpdate();
if (count > 0) { return true; }
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.release(rs, stmt, conn);
}
return false;
}
@Override
public Fruit findById(int fid) {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
String sql = "select * from t_fruit where fid = ? ";
stmt = conn.prepareStatement(sql);
stmt.setInt(1,fid);
rs = stmt.executeQuery();
while (rs.next()) {
Fruit fruit = new Fruit();
fruit.setFid(rs.getInt("fid"));
fruit.setFname(rs.getString("fname"));
fruit.setPrice(rs.getInt("price"));
fruit.setFcount(rs.getInt("fcount"));
fruit.setRemark(rs.getString("remark"));
return fruit;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
return null;
}
}
3、测试
package com.wnx.mall.tiny.fruit.dao;
import com.wnx.mall.tiny.fruit.dao.impl.FruitDAOImpl;
import com.wnx.mall.tiny.fruit.domain.Fruit;
import org.junit.Test;
import java.util.List;
public class FruitDaoTest {
//测试查询全部水果
@Test
public void testFindAll(){
FruitDAO fruitDAO = new FruitDAOImpl();
List fruitList = fruitDAO.getFruitList();
fruitList.forEach(System.out::println);
}
//测试添加水果
@Test
public void testAdd(){
Fruit fruit = new Fruit(null, "梨子", 5, 12, "30");
FruitDAO fruitDAO = new FruitDAOImpl();
fruitDAO.addFruit(fruit);
}
//测试更新水果
@Test
public void testUpdateFruit(){
Fruit fruit = new Fruit(5, "梨子", 5, 100, "30");
FruitDAO fruitDAO = new FruitDAOImpl();
fruitDAO.updateFruit(fruit);
}
//测试根据水果名称获取水果
@Test
public void testGetFruitByFname(){
String fname = "梨子";
FruitDAO fruitDAO = new FruitDAOImpl();
Fruit fruit = fruitDAO.getFruitByFname(fname);
System.out.println(fruit);
}
//测试根据水果名称删除水果
@Test
public void testDelFruit(){
String fname = "梨子";
FruitDAO fruitDAO = new FruitDAOImpl();
fruitDAO.delFruit(fname);
}
//测试根据ID查询水果
@Test
public void testFindById(){
Integer fid = 1;
FruitDAO fruitDAO = new FruitDAOImpl();
Fruit fruit = fruitDAO.findById(1);
System.out.println(fruit);
}
//测试根据ID删除水果
@Test
public void testDeleteById(){
Integer fid = 10;
FruitDAO fruitDAO = new FruitDAOImpl();
fruitDAO.delete(fid);
}
}
4、缺点
这种方式虽然可以操作数据库，并使用了工具类对步骤进行了抽离，但是步骤依旧繁琐。而目前业务的核心，只有SQL语句，能不能让代码更加智能呢？-答案：反射！
