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();
}
}