DBuitils之增删改查

JDBC工具类

package com.itheima.jdbc.utils;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
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 JDBCUitel1 {
		
		private static DataSource dataSource=null;//初始化连接池
		
		static {
			
		//创建连接池
		Properties properties = new Properties();
		try {
			
		properties.load(new FileInputStream("src/druid.properties"));//读取配置文件，设置参数
		dataSource = DruidDataSourceFactory.createDataSource(properties);
		
		}catch(Exception e) {
			e.printStackTrace();
		}
		
		}
		
	
		public static Connection getconnection() {
			Connection conn = null;
			try {
				
			conn = dataSource.getConnection();
			//获取连接池中的连接
				
			}catch(Exception e) {
				e.printStackTrace();
			}
			return conn;
		}
		public static DataSource getdatasource() {
			return dataSource;
			
			
		}

			public static void release(Statement stmt,Connection conn) {
						if(stmt!=null) {
							try {
								stmt.close();
							}catch(SQLException e) {
								e.printStackTrace();
							}
							stmt=null;
						}
							
					
						if(conn!=null) {
							
							try {
								conn.close();
							}catch(SQLException e) {
								e.printStackTrace();
							}
							conn=null;
						}
			}
			public static void release(ResultSet rs,Statement stmt,Connection conn) {
				if(stmt!=null) {
					try {
						stmt.close();
					}catch(SQLException e) {
						e.printStackTrace();
					}
					stmt=null;
				}
					
			
				if(conn!=null) {
					
					try {
						
						conn.close();
					}catch(SQLException e) {
						e.printStackTrace();
					}
					conn=null;
				}
				if(rs!=null) {
						
						try {
							rs.close();
						}catch(SQLException e) {
							e.printStackTrace();
						}
						rs=null;
					}
}
}

配置文件

url:jdbc:mysql://localhost/web_test4?serverTimezone=UTC
driverClassName:com.mysql.cj.jdbc.Driver
username:root
password:123456

用户类

package com.itheima.DBuitls.daouser;

public class user {
	private Integer id;
	private String username;
	private Double money;
	//在eclipse中通过 shift+alt+s快捷键可以快速构建get、set方法和重写toString方法
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public Double getMoney() {
		return money;
	}
	public void setMoney(Double money) {
		this.money = money;
	}
	
	@Override
	public String toString() {
		return "user [id=" + id + ", username=" + username + ", money=" + money + "]";
	}
	

}

测试类

package com.itheima.DButils;

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

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

import com.itheima.DBuitls.daouser.user;
import com.itheima.jdbc.utils.JDBCUitel1;

public class DBuitils {
	
	@Test
	public void dome1() throws SQLException {
		QueryRunner queryrunner = new QueryRunner(JDBCUitel1.getdatasource());
		queryrunner.update("insert into user values (null,?,?);", "阿黄","20000");
		//DBuitils之增加数据
		
	}
	
	@Test
	public void dome2() throws SQLException {
		QueryRunner queryrunner = new QueryRunner(JDBCUitel1.getdatasource());
		queryrunner.update("delete from user where id=?", 6);
		//DBuitils之删除数据
		
	}
	@Test
	public void dome3() throws SQLException {
		QueryRunner queryrunner = new QueryRunner(JDBCUitel1.getdatasource());
		queryrunner.update("update user set username=?,money=? where id = ?", "旺财","100000",3);
		//DBuitils之更改数据
		
	}
	@Test
	public void demo4() throws SQLException {
		//查询一条记录
		QueryRunner queryrunner = new QueryRunner(JDBCUitel1.getdatasource());
		
		user rs = queryrunner.query("select * from user where id=?", new BeanHandler<user>(user.class), 2);
		//<>括号中的内容是要返回的数据，这里填写的是user所以会返回一个user对象，这个对象中保存着查询的数据
		
		System.out.println(rs);
		
	}
	@Test
	public void demo5() throws SQLException {
		// 查询多条数据
		QueryRunner queryrunner = new QueryRunner(JDBCUitel1.getdatasource());
		List<user> list= queryrunner.query("select * from user", new BeanListHandler<user>(user.class));
		//用list集合存储
		for(user a:list) {
			//增强for输出
			System.out.println(a);
		}
	}

}