使用JDBC实现增删改查（加入Druid德鲁伊连接池）

使用JDBC实现增删改查（加入Druid（德鲁伊）连接池）

---

使用jdbc（加入数据库连接池Druid）完成Student表的增删改查四个功能的操作

• 添加jdbc的连接驱动jar包
• 添加druidjar包

1. 创建数据库

CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(50) NOT NULL,
	sex VARCHAR(10) DEFAULT 'man',
	age VARCHAR(5)
) CHARSET = utf8;

2. 编写Student.jsp实现类

package com.kaka.test_03;
public class Student {
	private int id;
	private String name;
	private String sex;
	private String age;
	public Student() {
		super();
	}
	public Student(int id, String name, String sex, String age) {
		super();
		this.id = id;
		this.name = name;
		this.sex = sex;
		this.age = age;
	}
	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 getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public String getAge() {
		return age;
	}
	public void setAge(String age) {
		this.age = age;
	}
	@Override
	public String toString() {
		return "Student [id=" + id + ", name=" + name + ", sex=" + sex + ", age=" + age + "]";
	}	
}

3. 编写druid.properties文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/db0711?useUnicode=true&characterEncoding=utf8
username=root
password=root
#初始化连接
initialSize=10
#最大连接数量
maxActive=30
#最小空闲连接
minIdle=5
#超时等待时间
maxWait=5000

4. 编写JDBCUtil.java工具类

package com.kaka.test_03;
import java.io.IOException;
import java.io.InputStream;
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 {
	//获取数据库连接池
	private static DataSource ds;
	
	//提供静态代码块
	//加载配置文件
	static{
		try{
			//创建Properties集合类对象
			Properties prop = new Properties();
			//加载配置文件
			InputStream inputStream = JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties");
			
			//加载
			prop.load(inputStream);
			inputStream.close();
			
			//创建连接池对象
			ds = DruidDataSourceFactory.createDataSource(prop);
			
		} catch(IOException e){
			e.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	//获取连接
	public static Connection getConnection() throws SQLException {
		return ds.getConnection() ;
	}
	
	//定义一个方法,获取存在的连接池
	public static DataSource getDataSource() {
		return ds ;
	}
	
	//释放资源
	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();
			}
		}
	}
}

5. 编写StuDaoImpl.java接口实现类

package com.kaka.test_03;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StuDaoImpl {
	private Connection conn = null;
	private PreparedStatement stmt = null;
	private ResultSet rs = null;
	
	//增加
	public int insert(Student stu){
		try {
			//数据库连接对象
			conn = JDBCUtils.getConnection();
			//编写SQL语句
			String sql = "insert into student (id,name,sex,age) values(?,?,?,?);";
			//预编译对象
			stmt = conn.prepareStatement(sql);
			//执行更新
			stmt.setInt(1, stu.getId());
			stmt.setString(2, stu.getName());
			stmt.setString(3, stu.getSex());
			stmt.setString(4, stu.getAge());
			
			int i = stmt.executeUpdate();
			return i;
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			JDBCUtils.close(stmt, conn);
		}
		return 0;
	}
	
	//删除
	public int delete (int id){
		
		try {
			conn = JDBCUtils.getConnection();
			String sql = "delete from student where id = ?";
			stmt = conn.prepareStatement(sql);
			//获取id
			stmt.setInt(1, id);
			return stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.close(stmt, conn);
		}
		return 0;
	}
	
	//修改
	public int update(Student stu){
		
		try {
			conn = JDBCUtils.getConnection();
			String sql = "update student set name = ?,sex = ?,age = ? where id = ?";
			stmt = conn.prepareStatement(sql);
			//执行更新
			stmt.setString(1, stu.getName());
			stmt.setString(2, stu.getSex());
			stmt.setString(3, stu.getAge());
			stmt.setInt(4, stu.getId());
			return stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.close(stmt, conn);
		}
		return 0;
	}
	
	//查询
	public List<Student> selectAll(){

		 try {
			conn = JDBCUtils.getConnection();
			String sql = "select id,name,sex,age from student";
			List<Student> stuList = new ArrayList<Student>();
			 stmt = conn.prepareStatement(sql);
             rs = stmt.executeQuery();
             while(rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String sex = rs.getString("sex");
                String age = rs.getString("age");
                Student  stu = new Student(id, name, sex, age);
               //每封装完一个对象，添加到集合当中
                stuList.add(stu);
            }
            return stuList;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
        	JDBCUtils.close(rs, stmt, conn);
        }
		return null;
	}
}

6. 编写Test.java测试类

package com.kaka.test_03;
import java.util.List;

public class Test {
	public static void main(String[] args) {
		
		StuDaoImpl sdi = new StuDaoImpl();
		//新增
		inserts(sdi);
		
		//删除
		deletes(sdi);
		
		//修改
		undates(sdi);
		
		//查询
		selects(sdi);
	}

	private static void selects(StuDaoImpl sdi) {
		List<Student> stuList = sdi.selectAll();
		stuList.forEach(System.out::println);
	}

	//修改
	private static void undates(StuDaoImpl sdi) {
		Student emp = new Student(1,"赵六","男","43");
		int result = sdi.update(emp);
		System.out.println(result);
	}
	
	
	//删除
	private static void deletes(StuDaoImpl sdi) {
		int result = sdi.delete(5);
		System.out.println(result);
	}
	
	//新增
	private static void inserts(StuDaoImpl sdi) {
		Student stu = new Student(5,"name4","man","23");
		int result = sdi.insert(stu);
		if(result > 0){
			System.out.println("success");
		}else{
			System.out.println("defeat");
		}
	}
}

7. 运行结果

7.1 查询数据

7.2 新增数据

7.3 删除数据

7.4 修改数据