一.Druid连接池(阿里云)
1.配置文件 druid.properties
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/dsm?useSSL=false&serverTimezone=UTC&characterEncoding=utf8
username=root
password=123456
连接池参数(可以不写):默认连接池连接数,最大连接池连接数,时间
initialSize=5
maxActive=10
maxWait=3000
2.Druid连接池工具类
public class JDBCUtils3 {
// 1.定义数据源成员属性
private static DataSource ds;
// 2.在静态代码块中加载文件
static {
try {
Properties prop = new Properties();
prop.load(new FileInputStream("src/druid.properties"));
// 3.创建连接池对象
ds = DruidDataSourceFactory.createDataSource(prop);
}catch (Exception e){
e.printStackTrace();
}
}
// 调用方法获取对象
public static Connection getConn() throws SQLException {
return ds.getConnection();
}
// 调用方法获取连接池对象
public static DataSource getDataSource(){
return ds;
}
public static void close(PreparedStatement ps, Connection con){
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(con!=null){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//释放资源的方法--针对 查询
public static void close(PreparedStatement ps, Connection con, ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
close(ps,con);//调用上面重载的close方法
}
}
3.使用Druid连接池
public class Druid02 {
private Connection conn=null;
private PreparedStatement ps=null;
private ResultSet rs=null;
@Test
public void test01(){
try {
DataSource ds = JDBCUtils3.getDataSource();
conn = JDBCUtils3.getConn();
String sql="select * from dsm where password=?;";
ps = conn.prepareStatement(sql);
ps.setInt(1,123456);
rs = ps.executeQuery();
while (rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
String ac = rs.getString("accountnumber");
int pwd = rs.getInt("password");
double money = rs.getDouble("money");
System.out.println(id+"\t\t"+name+"\t\t"+ac+"\t\t"+pwd+"\t\t"+money);
}
/**
* CreateTime : 连接时间(年月日 时分秒)
* ActiveCount :
* PoolingCount :
* CreateCount :
* DestroyCount :
* CloseCount :
* ConnectCount :
* Connections :
*/
System.out.println(ds);
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils3.close(ps,conn,rs);
}
}
}
二.jdbcTemplate
1. 简介
- Spring框架对jdbc的简单封装,提供了一个JdbcTemplate对象
- jdbcTemplate的若干方法
- update 执行 dml增删改
- queryForMap() 查询结果,将结果集封装为map集合
- queryForList() 查询结果,将结果集封装为list集合
- queryForObject() 查询结果集,将结果集封装成某个数据类型
- query() 查询结果集,将结果集封装为Javabean对象
2.案例,练习
// 结合Druid连接池工具类
@Test //update方法
public void test01(){
// 1.导入架包
// 2.创建jdbcTemplate对象
JdbcTemplate template = new JdbcTemplate(JDBCUtils3.getDataSource());
// 3.定义sql语句
// 修改
//String sql1="update dsm set money=? where id=?";
//int update = template.update(sql1,11111,3);
// 删除
//String sql2="delete from dsm where id=?";
//int update1 = template.update(sql2, 2);
// 添加
String sql3="insert into dsm values (null,?,?,?,?,?);";
int update2 = template.update(sql3, "火千军", "hqj", 125125, 50000,null);
//System.out.println(update);
//System.out.println(update1);
System.out.println(update2);
}
@Test//查询方法 queryForMap() 查询结果,将结果集封装为map集合
public void test02(){
// 1.导入架包
// 2.创建jdbcTemplate对象
JdbcTemplate template = new JdbcTemplate(JDBCUtils3.getDataSource());
// 查询id为3的一行信息,封装map
Map<String, Object> map = template.queryForMap("select * from dsm where id=?", 3);
Set<Map.Entry<String, Object>> entries = map.entrySet();
for (Map.Entry<String, Object> entry : entries) {
System.out.println(entry.getKey()+"\t\t"+entry.getValue());
}
System.out.println(map);
}
@Test// 查询一行信息,封装javaBean
public void test03(){
JdbcTemplate template = new JdbcTemplate(JDBCUtils3.getDataSource());
Dsm dsm = template.queryForObject("select * from dsm where id=?", new BeanPropertyRowMapper<Dsm>(Dsm.class), 3);
System.out.println(dsm);
System.out.println(dsm.toString());
}
@Test// 查询一组信息,封装成 list 中泛型为 map
public void test04(){
JdbcTemplate template = new JdbcTemplate(JDBCUtils3.getDataSource());
List<Map<String, Object>> list = template.queryForList("select * from dsm");
System.out.println("id\t\t\tname\t\t\t账号\t\t\t密码\t\t\t余额");
for (Map<String, Object> map1 : list) {
Set<Map.Entry<String, Object>> entries = map1.entrySet();
for (Map.Entry<String, Object> entry : entries) {
System.out.print(entry.getValue()+"\t\t\t");
}
System.out.println();
}
}
@Test// 查询一组信息,封装成 list 中泛型为 JavaBean
public void test05(){
JdbcTemplate template = new JdbcTemplate(JDBCUtils3.getDataSource());
List<Dsm> list = template.query("select * from dsm", new BeanPropertyRowMapper<Dsm>(Dsm.class));
System.out.println(list);
for (Dsm dsm : list) {
System.out.println(dsm.toString());
System.out.println("=========================");
}
}
@Test//查询总记录数
public void test06(){
JdbcTemplate template = new JdbcTemplate(JDBCUtils3.getDataSource());
Integer a = template.queryForObject("select count(*) from dsm", Integer.class);
System.out.println(a);
}
3.表查询加封装
1.根据属性定义类
// 员工表(员工类)
public class Dsm {
private Integer id;
private String name;
private String zh;
private Integer password;
private Double money;
private Integer bm_id;
private Bm bm; // 关联部门信息(员工中关联部门)
// 有无参构造方法,封装方法,重写toString方法略去
}
…
// 部门表(部门类)
public class Bm {
private Integer id;
private String name;
private List<Dsm> dsms;// 员工部门存在多个员工(部门中关联员工)
// 有无参构造方法,封装方法,重写toString方法略去
}
2.连表查询及封装
@Test//连表查询 封装
public void test08(){
JdbcTemplate template = new JdbcTemplate(JDBCUtils3.getDataSource());
List<Dsm> dsms = template.query("select * from dsm", new BeanPropertyRowMapper<Dsm>(Dsm.class));
for (Dsm dsm : dsms){
dsm.setBm(template.queryForObject("select * from bm where id=?", new BeanPropertyRowMapper<Bm>(Bm.class), dsm.getBm_id()));
}
System.out.println(dsms);
}
@Test//连表查询 封装
public void test09(){
JdbcTemplate template = new JdbcTemplate(JDBCUtils3.getDataSource());
List<Dsm> dsms = template.query("select * from dsm", new BeanPropertyRowMapper<Dsm>(Dsm.class));
for (Dsm dsm : dsms) {
Bm bm = template.queryForObject("select * from bm where id=?", new BeanPropertyRowMapper<Bm>(Bm.class), dsm.getBm_id());
System.out.println(bm);
dsm.setBm(bm);
}
System.out.println(dsms);
}
3.模拟查询和封装的实现
public class Moli {
@Test//启动模拟查询和封装的实现的方法
public void test02(){
List<Dsm> ml = ml("select * from dsm where id>?", Dsm.class, 7);
System.out.println(ml);
}
// 模拟查询和封装的实现
public <T> List<T> ml(String sql , Class<T> c , Object ...args ){
try {
// 根据工具类获取连接池中的数据库连接对象
Connection conn = JDBCUtils3.getConn();
// 获取执行sql的对象
//System.out.println(sql);
PreparedStatement ps = conn.prepareStatement(sql);
// 给占位符赋值
for (int i = 0; i < args.length; i++) {
ps.setObject(i+1,args[i]);
}
// 执行sql语句
ResultSet rs = ps.executeQuery();
// 处理结果 -- 拆开rs
// 元数据
ResultSetMetaData md = rs.getMetaData();
int columnCount = md.getColumnCount();
ArrayList<T> ts = new ArrayList<>();
while (rs.next()){// 外层循环遍历每一行
T t = c.newInstance();
for (int i = 1; i <= columnCount; i++) { // 内层循环遍历每一行的每一个单元格
Object value = rs.getObject(i);
String columnName = md.getColumnName(i).toLowerCase();// 获取每一个列的列名 -- 和T中的属性名一致的( 必须完全一致 )
//System.out.println(columnName);
Field f = c.getDeclaredField(columnName);
f.setAccessible(true);// 破开私有属性
f.set(t,value);
}
ts.add(t);
}
return ts;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
@Test
public void test01(){
JdbcTemplate template = new JdbcTemplate(JDBCUtils3.getDataSource());
List<Dsm> list = template.query("select * from dsm", new BeanPropertyRowMapper<Dsm>(Dsm.class));
System.out.println(list);
}
}