当系统初始化好后,容器被创建,容器中会申请一些连接对象,当用户来访问数据库时,从容器中获取连接对象,用户访问完后,会将连接对象归还给容器
节约资源
用户访问高效
package org.ccit.util; /** * Druid连接池的工具类 * @author yinshuang * */ import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; import javax.sql.DataSource; import com.alibaba.druid.pool.DruidDataSourceFactory; public class JDBCUtils { //1.定义成员变量 DataSource private static DataSource ds; static { try { //1.加载配置文件 Properties pro = new Properties(); pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties")); //2.获取DataSource ds = DruidDataSourceFactory.createDataSource(pro); } catch (Exception e) { e.printStackTrace(); } } /** * 获取连接 * @throws SQLException */ public static Connection getConnection() throws SQLException { return ds.getConnection(); } /** * 释放资源 */ public static void close(Statement stmt,Connection conn) { close(null, stmt, conn); } /** * 释放资源 */ public static void close(ResultSet rs,Statement stmt,Connection conn) { if(rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if(stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn != null) { try { conn.close();//归还连接 } catch (SQLException e) { e.printStackTrace(); } } } /** * 获取连接池方法 */ public static DataSource getDataSource() { return ds; } }
package org.ccit.druid; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import org.ccit.util.JDBCUtils; /** * 使用新的工具类 * @author yinshuang * */ public class DruidDemo2 { public static void main(String[] args) { /** * 添加操作:添加一条记录 */ Connection conn = null; PreparedStatement pstmt = null; try { //1. 获取连接 conn = JDBCUtils.getConnection(); //2.定义sql String sql = "insert into goods value(null,?,?,?,?)"; //3.获取prepareStatement对象 pstmt = conn.prepareStatement(sql); //4.给 ? 赋值 pstmt.setString(1, "橙汁"); pstmt.setInt(2, 30); pstmt.setDate(3, null); pstmt.setDouble(4, 50.5); //5.执行sql int count = pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally { //6.释放资源 JDBCUtils.close(pstmt, conn); } } }
Spring框架对JDBC的简单封装。提供了一个JDBCTemplate对象简化JDBC的开发
package org.ccit.entity; import java.util.Date; public class Goods { private int id; private String name;//名字 private int cnt;//数量 private Date best_date;//有效期 private double price;//价格 public Goods() { super(); } public Goods(int id, String name, int cnt, Date best_date, double price) { super(); this.id = id; this.name = name; this.cnt = cnt; this.best_date = best_date; this.price = price; } 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 int getCnt() { return cnt; } public void setCnt(int cnt) { this.cnt = cnt; } public Date getBest_date() { return best_date; } public void setBest_date(Date best_date) { this.best_date = best_date; } public double getPrice() { return price; } public void setPrice(double price) { this.price = price; } @Override public String toString() { return "Goods [id=" + id + ", name=" + name + ", cnt=" + cnt + ", best_date=" + best_date + ", price=" + price + "]"; } }
package org.ccit.template; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Date; import java.util.List; import java.util.Map; import org.ccit.entity.Goods; import org.ccit.util.JDBCUtils; import org.junit.Test; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; public class JdbcTemplateDemo2 { //1.获取JDBCTemplate对象 private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource()); @Test /** * 1.将id=1的cnt 修改为100 */ public void test1() { //2.定义sql String sql = "update goods set cnt=100 where id=1"; //3.执行sql int count = template.update(sql); System.out.println(count); } @Test /** * 2.添加一条记录 */ public void test2() { String sql = "insert into goods values(default,?,?,?,?)"; int count = template.update(sql,"火腿",50,"2021-09-03",56.2); System.out.println(count); } @Test /** * 3.删除记录 */ public void test3() { String sql = "delete from goods where name=?"; int count = template.update(sql,"火腿"); System.out.println(count); } @Test /** * 4.查询id=1的记录,将其封装为map集合 * 注:查询的结果集长度只能为1 */ public void test4() { String sql = "select * from goods where id=?"; //key:列名 value:值 Map<String, Object> map = template.queryForMap(sql,1); System.out.println(map); //{id=1, name=面包, cnt=100, best_date=null, price=99.1} } @Test /** * 5.查询所有记录,将其封装在List集合 */ public void test5() { String sql = "select * from goods"; List<Map<String, Object>> list = template.queryForList(sql); for(Map<String, Object> map :list) { System.out.println(map); } } @Test /** * 6_1.查询所有记录,将其封装在Goods对象的List集合 */ public void test6_1() { String sql = "select * from goods"; List<Goods> list = template.query(sql, new RowMapper<Goods>() { @Override public Goods mapRow(ResultSet rs, int i) throws SQLException { int id = rs.getInt("id"); String name = rs.getString("name"); int cnt = rs.getInt("cnt"); Date best_date = rs.getDate("best_date"); double price = rs.getDouble("price"); Goods goods = new Goods(id, name, cnt, best_date, price); return goods; } }); for(Goods goods:list) { System.out.println(goods); } } @Test /** * 6_2.查询所有记录,将其封装在Goods对象的List集合 */ public void test6_2() { String sql = "select * from goods"; List<Goods> list = template.query(sql, new BeanPropertyRowMapper<Goods>(Goods.class)); for(Goods goods : list) { System.out.println(goods); } } @Test /** *7.查询总记录数 */ public void test7() { String sql = "select count(id) from goods"; Long total = template.queryForObject(sql, Long.class); System.out.println(total); } }