java连接mysql增删改查（06通用增删改）

00.创建数据库

// 1)、创建数据库
CREATE DATABASE jdbc DEFAULT CHARACTER SET UTF8;
// 2)、切换数据库
USE jdbc;
// 3)、创建数据库表
CREATE TABLE user(
`user_id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
`user_name` VARCHAR(20) NOT NULL COMMENT '用户名',
`price` double(10,2) DEFAULT 0.0 COMMENT '价格',
`create_time` DATETIME DEFAULT NULL COMMENT '创建时间'
)ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=UTF8 COMMENT="用户表";
// 4)、插入用户
INSERT INTO user(user_name,price,create_time)VALUES('admin',100.12,now());

 01.在src目录下创建配置文件db.properties

jdbc_url=jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf-8
jdbc_user=root
jdbc_passwd=root

02.创建工具类JdbcUtil3.java

package com.detian;
 
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
 
public class JdbcUtil3 {
	private static  String JDBC_URL=null;
	private static  String USER_NAME=null;
	private static  String PASSWORD=null;
 
	//装我们的连接
	private static ThreadLocal<Connection> pool = new ThreadLocal<Connection>();
	
	//静态代码块
	static {
		//加载外部属性资源文件
		Properties p = new Properties();
		//类加载器
		InputStream in = JdbcUtil3.class.getClassLoader().getSystemResourceAsStream("db.properties");
		try {
			p.load(in);
			JDBC_URL = p.getProperty("jdbc_url");
			USER_NAME = p.getProperty("jdbc_user");
			PASSWORD = p.getProperty("jdbc_passwd");
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	//获取连接
	public static Connection getConnection() throws ClassNotFoundException, SQLException {
		
		Connection connection = pool.get();
		if(connection==null) {
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection(JDBC_URL, USER_NAME, PASSWORD);
			pool.set(connection);
		}
		return connection;
	}
	//关闭连接
	public static void close()  {
		Connection connection = pool.get();
		if(connection!=null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		//移除此线程局部变量当前线程值
		pool.remove();
	}
}

03.创建文件BaseDao.java

package com.detian;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 通用数据库操作类
 * CRUD(增删改查) SELECT INSERT DELETE UPDATE
 * @author 63516
 * @param<T> Java端数据类型
 * T 代表着java类型
 * User -- user ： 将Java的一个一个对象保存到数据库中（表里面的一行一行数据）
 * user -- Java ： 将数据库表中一行一行的数据映射到我们的Java对象中
 * */
public class BaseDao<T> {
	
	/**
	 * 编写通用查询方法(单个)
	 * @param connection：数据库连接
	 * @param sql：sql语句
	 * @param clazz：映射到Java对象的类型
	 * @param args：参数列表
	 * @return
	 * @throws SQLException 
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 * @throws SecurityException 
	 * @throws NoSuchFieldException 
	 */
	public T getOne(Connection connection,String sql,Class<T> clazz,Object...args) throws SQLException, InstantiationException, IllegalAccessException, NoSuchFieldException, SecurityException {
		T t=null;
		//获取PreparedStatement对象对sql语句预编译
		PreparedStatement ps = connection.prepareStatement(sql);
		if(args!=null && args.length>0) {
			for(int i = 0; i < args.length;i++) {
				ps.setObject(i+1, args[i]);//给占位符？赋值
			}
		}
		//执行
		ResultSet rs = ps.executeQuery();

		if(rs.next()) {
			//调用当前T类的无参构造方法
			t =clazz.newInstance();
			//如果有数据，那么读取每一列数据
			//通过rs的方法获取当前sql语句操作所在的表的元数据信息
			ResultSetMetaData rsmd = rs.getMetaData();
			//获取变得列总数
			int columnCount = rsmd.getColumnCount();
			System.out.println("columnCount：" + columnCount);
			for(int i= 0; i < columnCount ; i++) {
				//获取当前列的值
				Object columnValue = rs.getObject(i+1);
				//获取数据库的列名
				String columnLabel = rsmd.getColumnLabel(i+1);//user_id -- userId
				//通过列名匹配Java对象中的属性名
				Field field = clazz.getDeclaredField(columnLabel);// 当前Java对象中的属性对象
				//开启权限
				field.setAccessible(true);
				//给当前查找到的属性赋值
				field.set(t, columnValue); 
			}
		}
		return t;
	}
	
	/**
	 * 编写通用查询方法(列表)
	 * @throws SQLException 
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 * @throws SecurityException 
	 * @throws NoSuchFieldException 
	 */
	public List<T> getList(Connection connection,String sql,Class<T> clazz,Object...args) throws SQLException, InstantiationException, IllegalAccessException, NoSuchFieldException, SecurityException{
		List<T> list = new ArrayList<>();
		PreparedStatement ps = connection.prepareStatement(sql);
		if(args!=null && args.length>0) {
			//给占位符赋值
			for(int i=0;i<args.length;i++) {
				ps.setObject(i+1, args[i]);//给占位符？赋值
			}
		}
		//执行
		ResultSet rs = ps.executeQuery();
		//通过rs的方法获取当前sql语句操作所在的表的元数据信息
		ResultSetMetaData rsmd = rs.getMetaData();
		//获取变得列总数
		int columnCount = rsmd.getColumnCount();
		System.out.println("columnCount：" + columnCount);
		while(rs.next()) {
			T t = clazz.newInstance();
			for(int i= 0; i < columnCount ; i++) {
				//获取当前列的值
				Object columnValue = rs.getObject(i+1);
				//获取数据库的列名
				String columnLabel = rsmd.getColumnLabel(i+1);//user_id -- userId
				//通过列名匹配Java对象中的属性名
				Field field = clazz.getDeclaredField(columnLabel);// 当前Java对象中的属性对象
				//开启权限
				field.setAccessible(true);
				//给当前查找到的属性赋值
				field.set(t, columnValue); 
			}
			
			
			
			list.add(t);
		}
		return list;
		
	}
	
	
	/**
	 * 通用增删改方法编写
	 * @param connection：数据库连接
	 * @param sql：sql语句
	 * @param args：参数列表
	 * @throws SQLException
	 */
	public void update(Connection connection,String sql,Object...args) throws SQLException {
		//预编译
		PreparedStatement ps = connection.prepareStatement(sql);//预编译 
		if(args!=null && args.length>0) {
			for(int i=0;i<args.length;i++) {
				ps.setObject(i+1, args[i]);
			}
		}
		int row = ps.executeUpdate();
		System.out.println(row);
	}
}

04.创建实体类User.java

package com.detian;

import java.util.Date;

public class User {

	private Integer userId;// user_id
	private String userName;// user_naem
	private Double price;//price
	private Date createTime;//create_time
	public Integer getUserId() {
		return userId; 
	}
	public void setUserId(Integer userId) {
		this.userId = userId;
	}
	public String getUserName() {
		return userName;
	}
	public void setUserName(String userName) {
		this.userName = userName;
	}
	public Double getPrice() {
		return price;
	}
	public void setPrice(Double price) {
		this.price = price;
	}
	public Date getCreateTime() {
		return createTime;
	}
	public void setCreateTime(Date createTime) {
		this.createTime = createTime;
	}
	public User() {
		
		// TODO Auto-generated constructor stub
	}
	public User(Integer userId, String userName, Double price, Date createTime) {
		super();
		this.userId = userId;
		this.userName = userName;
		this.price = price;
		this.createTime = createTime;
	}
	@Override
	public String toString() {
		return "User [userId=" + userId + ", userName=" + userName + ", price=" + price + ", createTime=" + createTime
				+ "]";
	}
	
}

05.CURD,新建文件TestBaseDao.java

package com.detian;

import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.util.List;

import org.junit.Test;




public class TestBaseDao {

	BaseDao<User> baseDao = new BaseDao<User>();
	
	@Test
	public void testGetOne() throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException, NoSuchFieldException, SecurityException {
		Connection connection = JdbcUtil3.getConnection();
		String sql = "SELECT user_id as  userId,user_name as userName,price,create_time as createTime FROM user WHERE user_id=?";
		User user = baseDao.getOne(connection, sql, User.class, 1013);
		System.out.println(user);
		
	}
	
	@Test
	public void testGetList() throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException, NoSuchFieldException, SecurityException {
		Connection connection = JdbcUtil3.getConnection();
		//错误的写法
		//String sql = "SELECT user_id as  userId,user_name as userName,price,create_time as createTime FROM user WHERE user_name LIKE '%'?'%'";
		//方法一
		//String sql = "SELECT user_id as  userId,user_name as userName,price,create_time as createTime FROM user WHERE user_name LIKE \"%\"?\"%\"";
		//List<User> list = baseDao.getList(connection, sql, User.class,"李");
		//方法二
		String sql = "SELECT user_id as  userId,user_name as userName,price,create_time as createTime FROM user WHERE user_name LIKE ?";
		List<User> list = baseDao.getList(connection, sql, User.class,"%李%");
		System.out.println(list);
		
	}
	 
	
	@Test
	public void testAdd() throws ClassNotFoundException, SQLException {

		Connection connection = JdbcUtil3.getConnection();
		String sql="INSERT INTO user(user_name,price,create_time) VALUES(?,?,?)";
		baseDao.update(connection, sql, "张三",188,new Date( new java.util.Date().getTime()));
		JdbcUtil3.close(); 
	}
	
	
}