一、MySQL环境配置
mysql8.0下载地址：MySQL :: Download MySQL Installer
mysql-java-connector8.0下载：MySQL :: Download Connector/Java
二、自定义JDBCUtils
1、自定义获取connection
项目结构
-
创建
jdbc.properties文件user=root password=sll520 url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true driverClass=com.mysql.cj.jdbc.Driver -
定义方法获取connection
public static Connection getConnection() throws Exception { // 推荐的得到IO流的绝对安全的路径，置于src文件夹下。 // String path = Thread.currentThread().getContextClassLoader().getResource("jdbc.properties").getPath(); // FileInputStream fileInputStream = new FileInputStream(path); // Properties properties = new Properties(); // properties.load(fileInputStream); // String user1 = properties.getProperty("user"); ResourceBundle bundle = ResourceBundle.getBundle("jdbc"); String user = bundle.getString("user"); String password = bundle.getString("password"); String url = bundle.getString("url"); String driverClass = bundle.getString("driverClass"); Class.forName(driverClass); Connection connection = DriverManager.getConnection(url, user, password); return connection; }
2、druid获得connection
-
配置druid的资源文件
druid.properties文件url=jdbc:mysql://localhost:3306/test username=root password=sll520 driverClassName=com.mysql.cj.jdbc.Driver initialSize=10 maxActive=10 -
获取druid连接池中的
Connection实例import com.alibaba.druid.pool.DruidDataSourceFactory; import javax.sql.DataSource; import java.io.InputStream; import java.sql.Connection; import java.util.Properties; /** * @ClassName DruidTest * @Description TODO * @Author Mr_X * @Date 2022/4/9 17:32 * @Version 1.0 */ public class DruidTest { public static Connection getConnectionTest() throws Exception { //这种方法需要硬编码，不推荐 // DruidDataSource source = new DruidDataSource(); // source.setUrl(); // source.setUsername(); // source.setDriverClassName(); // source.setPassword(); //以下通过工厂类来造DruidDataSource对象 // InputStream stream = this.getClass().getClassLoader().getResourceAsStream("druid.properties"); InputStream stream = Thread.currentThread().getContextClassLoader().getResourceAsStream("druid.properties"); Properties properties = new Properties(); properties.load(stream); DataSource dataSource = DruidDataSourceFactory.createDataSource(properties); Connection connection = dataSource.getConnection(); System.out.println(connection); return connection; } }
3、定义JDBCUtils
import java.io.FileInputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.ResourceBundle;
/**
* @ClassName MyJDBCUtils
* @Description TODO
* @Author Mr_X
* @Date 2022/4/4 16:25
* @Version 1.0
*/
public class MyJDBCUtils {
/**
* 得到MySQL连接
* @return
* @throws Exception
*/
public static Connection getConnection() throws Exception {
// 推荐的得到IO流的绝对安全的路径，置于src文件夹下。
// String path = Thread.currentThread().getContextClassLoader().getResource("jdbc.properties").getPath();
// FileInputStream fileInputStream = new FileInputStream(path);
// Properties properties = new Properties();
// properties.load(fileInputStream);
// String user1 = properties.getProperty("user");
ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
String user = bundle.getString("user");
String password = bundle.getString("password");
String url = bundle.getString("url");
String driverClass = bundle.getString("driverClass");
Class.forName(driverClass);
Connection connection = DriverManager.getConnection(url, user, password);
return connection;
}
/**
* 对数据表进行增删改操作，如果成功则返回true，如果失败则返回false
* @param sql
* @param args
* @return int
*/
public static boolean commonUpdateTables(String sql, Object... args) throws Exception {
Connection connection = getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
int i = preparedStatement.executeUpdate();
closeResource(connection,preparedStatement);
return i > 0;
}
/**
* 返回结果集合
* @param testClass
* @param sql
* @param args
* @param <T>
* @return
* @throws Exception
*/
public static <T> List<T> getQueryResultSet(Class<T> testClass, String sql, Object ...args) throws Exception {
Connection connection = getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(sql);
List<T> list = new ArrayList<>();
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i+1,args[i]);
}
ResultSetMetaData metaData = preparedStatement.getMetaData();
ResultSet resultSet = preparedStatement.executeQuery();
int columnCount = metaData.getColumnCount();
while(resultSet.next()){
T t = testClass.newInstance();
for (int i = 0; i < columnCount; i++) {
//获取列名
String columnLabel = metaData.getColumnLabel(i + 1);
//获取列值
Object columnValue = resultSet.getObject(i + 1);
//通过反射给t赋值
Field declaredField = testClass.getDeclaredField(columnLabel);
declaredField.setAccessible(true);
declaredField.set(t,columnValue);
}
list.add(t);
}
resultSet.close();
closeResource(connection,preparedStatement);
return list;
}
public static <T> T getQueryResult(Class<T> testClass, String sql, Object ...args) throws Exception {
Connection connection = getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i+1,args[i]);
}
ResultSetMetaData metaData = preparedStatement.getMetaData();
ResultSet resultSet = preparedStatement.executeQuery();
int columnCount = metaData.getColumnCount();
if(resultSet.next()){
T t = testClass.newInstance();
for (int i = 0; i < columnCount; i++) {
//获取列名
String columnLabel = metaData.getColumnLabel(i + 1);
//获取列值
Object culumnValue = resultSet.getObject(i + 1);
//通过反射给t赋值
Field declaredField = testClass.getDeclaredField(columnLabel);
declaredField.setAccessible(true);
declaredField.set(t,culumnValue);
}
return t;
}
resultSet.close();
closeResource(connection,preparedStatement);
return null;
}
/**
* 关闭资源
* @param connection
* @param statement
*/
public static void closeResource(Connection connection, Statement statement){
closeResource(connection,statement,null);
}
public static void closeResource(Connection connection, Statement statement,ResultSet resultSet){
try {
if(connection!=null){
connection.close();
}
if(statement!=null){
statement.close();
}
if(resultSet!=null){
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
三、使用封装的JDBC技术
1、dbutils
-
A. 下载地址：
-
B. 创建lib文件夹将dbutils的jar包添加到库中
-
C. dbutils核心源码
public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException { PreparedStatement stmt = null; ResultSet rs = null; T result = null; try { stmt = this.prepareStatement(conn, sql); this.fillStatement(stmt, params); rs = this.wrap(stmt.executeQuery()); result = rsh.handle(rs); } catch (SQLException e) { this.rethrow(e, sql, params); } finally { try { close(rs); } finally { close(stmt); } } return result; }查看ResultHandler的继承体系：
-
D. 使用方法
-
在test数据库中准备customer表
-
bean包下创建customer类(ORM思想)
public class Customer { private int id; private String name; private String email; private Date birth; public Customer() { super(); } public Customer(int id, String name, String email, Date birth) { super(); this.id = id; this.name = name; this.email = email; this.birth = birth; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public Date getBirth() { return birth; } public void setBirth(Date birth) { this.birth = birth; } @Override public String toString() { return "Customer [id=" + id + ", name=" + name + ", email=" + email + ", birth=" + birth + "]"; } } -
方法实现
- 插入/更新操作
QueryRunner runner = new QueryRunner(); conn = MyJDBCUtils.getConnection(); String sql = "insert into customers(name,email,birth)values(?,?,?)"; int insertCount = runner.update(conn, sql, "蔡徐坤","caixukun@126.com","1997-09-08"); System.out.println("添加了" + insertCount + "条记录");-
查询操作
//查询单条记录并实例化一个对应的对象customer QueryRunner runner = new QueryRunner(); conn = MyJDBCUtils.getConnection(); String sql = "select id,name,email,birth from customers where id = ?"; BeanHandler<Customer> handler = new BeanHandler<>(Customer.class); Customer customer = runner.query(conn, sql, handler, 23); System.out.println(customer); 查询多条记录并创建Customer类的List集合 QueryRunner runner = new QueryRunner(); conn = MyJDBCUtils.getConnection(); String sql = "select id,name,email,birth from customers where id < ?"; BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class); List<Customer> list = runner.query(conn, sql, handler, 23); list.forEach(System.out::println); //查询单条记录并创建对应的键值对 QueryRunner runner = new QueryRunner(); conn = MyJDBCUtils.getConnection(); String sql = "select id,name,email,birth from customers where id = ?"; MapHandler handler = new MapHandler(); Map<String, Object> map = runner.query(conn, sql, handler, 23); System.out.println(map); //查询多条记录并创建对应的键值对List集合 QueryRunner runner = new QueryRunner(); conn = MyJDBCUtils.getConnection(); String sql = "select id,name,email,birth from customers where id < ?"; MapListHandler handler = new MapListHandler(); List<Map<String, Object>> list = runner.query(conn, sql, handler, 23); list.forEach(System.out::println); //查询特殊值（针对聚合函数） QueryRunner runner = new QueryRunner(); conn = MyJDBCUtils.getConnection(); String sql = "select max(birth) from customers"; ScalarHandler handler = new ScalarHandler(); Date maxBirth = (Date) runner.query(conn, sql, handler); System.out.println(maxBirth); QueryRunner runner = new QueryRunner(); conn = MyJDBCUtils.getConnection(); String sql = "select count(*) from customers"; ScalarHandler handler = new ScalarHandler(); Long count = (Long) runner.query(conn, sql, handler); System.out.println(count); -
自定义实现ResultHandler实现匿名内部类
@Test public void testQuery7(){ Connection conn = null; try { QueryRunner runner = new QueryRunner(); conn = MyJDBCUtils.getConnection(); String sql = "select id,name,email,birth from customers where id = ?"; ResultSetHandler<Customer> handler = new ResultSetHandler<Customer>(){ @Override public Customer handle(ResultSet rs) throws SQLException { // System.out.println("handle"); // return null; // return new Customer(12, "成龙", "Jacky@126.com", new Date(234324234324L)); if(rs.next()){ int id = rs.getInt("id"); String name = rs.getString("name"); String email = rs.getString("email"); Date birth = rs.getDate("birth"); Customer customer = new Customer(id, name, email, birth); return customer; } return null; } }; Customer customer = runner.query(conn, sql, handler,23); System.out.println(customer); } catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally{ MyJDBCUtils.closeResource(conn, null); } }
-
2、Spring jdbcTemplate
下载地址：
jdbc-template
获取connection：
public class JdbcTemplateDemo1 {
public static void main(String[] args) {
//1.导入jar包
//2.创建JDBCTemplate对象
JdbcTemplate template = new JdbcTemplate(传入在druid中获得的DataSource实例);
//3.调用方法
String sql = "update account set balance = 5000 where id = ?";
int count = template.update(sql, 3);
System.out.println(count);
}
}
实现方法
import cn.itcast.domain.Emp;
import cn.itcast.utils.JDBCUtils;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
public class JdbcTemplateDemo2 {
//Junit单元测试，可以让方法独立执行
//1. 获取JDBCTemplate对象
private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
/**
* 1. 修改1号数据的 salary 为 10000
*/
@Test
public void test1(){
//2. 定义sql
String sql = "update emp set salary = 10000 where id = 1001";
//3. 执行sql
int count = template.update(sql);
System.out.println(count);
}
/**
* 2. 添加一条记录
*/
@Test
public void test2(){
String sql = "insert into emp(id,ename,dept_id) values(?,?,?)";
int count = template.update(sql, 1015, "郭靖", 10);
System.out.println(count);
}
/**
* 3.删除刚才添加的记录
*/
@Test
public void test3(){
String sql = "delete from emp where id = ?";
int count = template.update(sql, 1015);
System.out.println(count);
}
/**
* 4.查询id为1001的记录，将其封装为Map集合
* 注意：这个方法查询的结果集长度只能是1
*/
@Test
public void test4(){
String sql = "select * from emp where id = ? or id = ?";
Map<String, Object> map = template.queryForMap(sql, 1001,1002);
System.out.println(map);
//{id=1001, ename=孙悟空, job_id=4, mgr=1004, joindate=2000-12-17, salary=10000.00, bonus=null, dept_id=20}
}
/**
* 5. 查询所有记录，将其封装为List
*/
@Test
public void test5(){
String sql = "select * from emp";
List<Map<String, Object>> list = template.queryForList(sql);
for (Map<String, Object> stringObjectMap : list) {
System.out.println(stringObjectMap);
}
}
/**
* 6. 查询所有记录，将其封装为Emp对象的List集合
*/
@Test
public void test6(){
String sql = "select * from emp";
List<Emp> list = template.query(sql, new RowMapper<Emp>() {
@Override
public Emp mapRow(ResultSet rs, int i) throws SQLException {
Emp emp = new Emp();
int id = rs.getInt("id");
String ename = rs.getString("ename");
int job_id = rs.getInt("job_id");
int mgr = rs.getInt("mgr");
Date joindate = rs.getDate("joindate");
double salary = rs.getDouble("salary");
double bonus = rs.getDouble("bonus");
int dept_id = rs.getInt("dept_id");
emp.setId(id);
emp.setEname(ename);
emp.setJob_id(job_id);
emp.setMgr(mgr);
emp.setJoindate(joindate);
emp.setSalary(salary);
emp.setBonus(bonus);
emp.setDept_id(dept_id);
return emp;
}
});
for (Emp emp : list) {
System.out.println(emp);
}
}
/**
* 6. 查询所有记录，将其封装为Emp对象的List集合
*/
@Test
public void test6_2(){
String sql = "select * from emp";
List<Emp> list = template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
for (Emp emp : list) {
System.out.println(emp);
}
}
/**
* 7. 聚合函数查询
*/
@Test
public void test7(){
String sql = "select count(id) from emp";
Long total = template.queryForObject(sql, Long.class);
System.out.println(total);
}
}
861
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
