JDBC(三)

最新推荐文章于 2022-04-22 17:09:28 发布

qq_39095899

最新推荐文章于 2022-04-22 17:09:28 发布

阅读量105

点赞数

分类专栏： MySQL数据库文章标签： mysql 数据库 jdbc java

本文链接：https://blog.csdn.net/qq_39095899/article/details/105090903

版权

MySQL数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

JDBC（三）

实际开发中"连接数据库"或者"断开数据库"比较消耗资源,为了提高程序的性能,要使用数据库连接池,共享连接(connection)

连接池的方式:
1.DBCP:tomcat内置的连接池
2.C3P0:SSH中内置的连接池
3.Druid:阿里开源的连接池

三种连接池的Util包的配置都会有的
导入jar包

c3p0-0.9.1.2.jar
commons-dbcp-1.4.jar
commons-dbutils-1.7.jar
commons-pool-1.5.4.jar
mysql-connector-java-5.1.45-bin.jar

步骤一：配置util包

DBCP连接池 (主要看这个)

今天用DBCP连接池连接数据库

package com.util;
//数据库连接池的用法


import java.sql.Connection;
import java.sql.SQLException;

import org.apache.commons.dbcp.BasicDataSource;

public class DBCPUtil {
	
	//创建数据库连接池对象
	private static BasicDataSource basicDataSource = new BasicDataSource();
	//2.配置连接池
	static {
		basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
		basicDataSource.setUrl("jdbc:mysql://localhost:3306/school?characterEncoding=utf-8&&useSSL=true");
		basicDataSource.setUsername("root");
		basicDataSource.setPassword("123456");
	}
	//3.获取一个数据库连接对象
	public static Connection getConnection() {
		Connection connection = null;
		try {
			connection = basicDataSource.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connection;
	}
	//4.注意注意！！返回一个数据库连接池对象
	public static BasicDataSource getDataSource() {
		return basicDataSource;
	}
	
}

DBCP连接池只是不使用commons.dbutils包中封装的方法

package com.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.dbcp.BasicDataSource;


public class JDBCPoolUtil01 {
	//SSL: Secure  Socket  Layer 安全套接层是为了保证数据安全输出的协议
	//1.定义常量
	private static final String DRIVER = "com.mysql.jdbc.Driver";
	private static final String URL = "jdbc:mysql://localhost:3306/school?characterEncoding=utf-8&&useSSL=true";
	private static final String USERNAME = "root";
	private static final String PASSWORD = "1234";
	
	//2.记录需要关闭的对象
	private static Connection  connection = null;
	private static PreparedStatement statement = null;
	private static ResultSet resultSet = null;
	
	//3.数据库连接池对象
	public static BasicDataSource dataSource = new BasicDataSource();
	
	//4.数据库连接池的配置
	//使用静态代码块
	static {
		//1.驱动
		dataSource.setDriverClassName(DRIVER);
		//2.数据库地址
		dataSource.setUrl(URL);
		//3.账号
		dataSource.setUsername(USERNAME);
		//4.密码
		dataSource.setPassword(PASSWORD);
		
	}
	
	
	//5.从数据库连接池中,获取共享的connection连接对象
	public static Connection getConnection() {
		try {
			connection = dataSource.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connection;
	}
	//MySQL<---->连接池<---->Connection连接对象
	//6.关闭
	public static void close() {
		try {
			if(connection != null) {
				//注意,并不是真的断开跟数据库的连接(毕竟数据库连接是数据库连接池负责.这个close只是将连接对象重新放回连接池中)
				connection.close();
			}
			if(statement != null) {
				statement.close();
			}
			if (resultSet != null) {
				resultSet.close();
			}
			
			
		} catch (SQLException e) {
       e.printStackTrace();
		}
		
		
		
	}
	//-------------------------------------------------------------------
	
	//7.封装增删改.....
	public static int executeUpdate(String sql, Object... objs) {
		int row = 0;
		connection = getConnection();
		try {
			statement = connection.prepareStatement(sql);
			//遍历 查看是否有参数
			for (int i = 0; i < objs.length; i++) {
				statement.setObject(i + 1, objs[i]);
			}
			row = statement.executeUpdate();
			close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return row;
	}
	
	//8.封装查询(无论查询一条还是多条)
	public static ResultSet executeQuery(String sql, Object...objects) {
		connection = getConnection();

		try {
			statement = connection.prepareStatement(sql);
			for (int i = 0; i < objects.length; i++) {
				statement.setObject(i + 1, objects[i]);
			}
			resultSet = statement.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return resultSet;
	}
	
}

C3P0连接池util包配置

package com.util;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class JDBCPoolUtil02 {

	//1.定义常量
		private static final String DRIVER = "com.mysql.jdbc.Driver";
		private static final String URL = "jdbc:mysql://localhost:3306/school?characterEncoding=utf-8&&useSSL=true";
		private static final String USERNAME = "root";
		private static final String PASSWORD = "1234";
		
		//2.记录需要关闭的对象
		private static Connection  connection = null;
		private static PreparedStatement statement = null;
		private static ResultSet resultSet = null;
	
		//3.测试c3p0连接池 
		private static ComboPooledDataSource  dataSource = new ComboPooledDataSource();
		
		//4.配置连接池
		static {
			try {
				//1.驱动
				dataSource.setDriverClass(DRIVER);
				//2.数据库地址
				dataSource.setJdbcUrl(URL);
				//3.用户名
				dataSource.setUser(USERNAME);
				//4.密码
				dataSource.setPassword(PASSWORD);
				//5.设置最大连接数
				dataSource.setMaxPoolSize(10);
				//6.设置最小连接数
				dataSource.setMinPoolSize(3);
				//7.初始的连接数量
				dataSource.setInitialPoolSize(5);
				
			} catch (PropertyVetoException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		//5.从数据库连接池中,获取共享的connection连接对象
		public static Connection getConnection() {
			try {
				connection = dataSource.getConnection();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return connection;
		}
		//6.关闭
		public static void close() {
			try {
				if(connection != null) {
					//注意,并不是真的断开跟数据库的连接(毕竟数据库连接是数据库连接池负责.这个close只是将连接对象重新放回连接池中)
					connection.close();
				}
				if(statement != null) {
					statement.close();
				}
				if (resultSet != null) {
					resultSet.close();
				}
				
				
			} catch (SQLException e) {
	       e.printStackTrace();
			}
			
			
			
		}
		//-------------------------------------------------
		//7.封装增删改.....
		public static int executeUpdate(String sql, Object... objs) {
			int row = 0;
			connection = getConnection();
			try {
				statement = connection.prepareStatement(sql);
				//遍历 查看是否有参数
				for (int i = 0; i < objs.length; i++) {
					statement.setObject(i + 1, objs[i]);
				}
				row = statement.executeUpdate();
				close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			return row;
		}
		
		//8.封装查询(无论查询一条还是多条)
		public static ResultSet executeQuery(String sql, Object...objects) {
			connection = getConnection();

			try {
				statement = connection.prepareStatement(sql);
				for (int i = 0; i < objects.length; i++) {
					statement.setObject(i + 1, objects[i]);
				}
				resultSet = statement.executeQuery();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			return resultSet;
		}
		
	
}

步骤二：bean包

此处就省略啦跟上一篇用的同一个数据库，所以是同一个javaBean

步骤三：连接数据库dao包

创建 InterfaceStudent接口

package com.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.bean.Student;

public interface InterfaceStudent {
	//添加学生
	 int insertStudent(String name, String gender, int birthday, String department, String adress);
	//删除学生
	 int deleteStudentByNumber(int id);
	 //修改学生信息
	 int updateStudent(int id, String name, String gender, int birthday, String department, String adress);
	//查询全部学生
	 List<Student> selectAllStudnet();
	 //查询一个学生
	 Student selectOneStudentBy(int id);
	 //查询所有学生的名字
	 List<String> selectAllStudentName();
	//查询一个学生的名字
	 Map<String, Object> selectOneStudentNameBy(int id);
}

创建实现类 ImpStudent

package com.dao;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import com.bean.Student;
import com.util.DBCPUtil;

public class ImpStudent implements InterfaceStudent {

	@Override
	public int insertStudent(String name, String gender, int birthday, String department, String adress) {
		int row = 0;
		//创建数据库连接池提供的执行命令的对象
		QueryRunner runner = new QueryRunner(DBCPUtil.getDataSource());
		try {
			row = runner.update("insert into student set(name,gender,bithday,department,adress) valus(?,?,?,?,?) ", name,gender,birthday,department,adress);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return row;
	}

	@Override
	public int deleteStudentByNumber(int id) {
		int row = 0;
		QueryRunner queryRunner = new QueryRunner(DBCPUtil.getDataSource());
		try {
			row = queryRunner.update("delete from student where id=?",id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return row;
	}

	@Override
	public int updateStudent(int id, String name, String gender, int birthday, String department, String adress) {
		int row = 0;
		QueryRunner queryRunner = new QueryRunner(DBCPUtil.getDataSource());
		try {
			row = queryRunner.update("update student set name=?, gender=?,birthday=?,department=?,adress=? where id=?",name,gender,birthday,department,adress,id );
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return row;
	}

	@Override
	public List<Student> selectAllStudnet() {
		List<Student> studentList = null;
		QueryRunner queryRunner = new QueryRunner(DBCPUtil.getDataSource());
		//关键 不要再一条一条的封装对象模型
 		try {
			studentList = queryRunner.query("select * from student", new BeanListHandler<Student>(Student.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return studentList;
	}
	//注意：查询多个人是BeanListHandler 查询一个BeanHandler
	@Override
	public Student selectOneStudentBy(int id) {
		String sql = "select * from student where id=?";
		Student student = null;
		QueryRunner queryRunner = new QueryRunner(DBCPUtil.getDataSource());
		try {
			student = queryRunner.query(sql, new BeanHandler<Student>(Student.class),id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return student;
	}
	//注意：如果只是查询某一个列或者某几列 ,就要考虑是否符合bean包类的属性！！
	//只查询某列/几列 可以使用Map存储
	@Override
	public List<String> selectAllStudentName() {
		QueryRunner queryRunner = new QueryRunner(DBCPUtil.getDataSource());
		List list = null;
		try {
			list = queryRunner.query("select name from student", new MapListHandler());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public Map<String, Object> selectOneStudentNameBy(int id) {
		QueryRunner queryRunner = new QueryRunner(DBCPUtil.getDataSource());
		Map<String, Object> map = null;
		try {
			map=queryRunner.query("select name,gender from student where id=?", new MapHandler(),id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return map;
	}

}

步骤五：测试

package com.main;

import java.util.List;
import java.util.Map;
import com.bean.Student;
import com.dao.ImpStudent;

public class Main {

	public static void main(String[] args) {
		//查询全部学生
		ImpStudent impStudent = new ImpStudent();
		List<Student> studnetList = impStudent.selectAllStudnet();
		for (Student stu : studnetList) {
			System.out.println(stu);
			
		}
		
		int row = impStudent.insertStudent("品如", "女", 1988,"艺术系", "河南信息");
		System.out.println(row > 0 ? "插入成功" : "插入失败");
		
		row = impStudent.updateStudent(8, "玫瑰花", "女", 1988,"艺术系", "河南信息");
		System.out.println(row > 0 ? "修改成功" : "修改失败");
		
		
		row = impStudent.deleteStudentByNumber(7);
		System.out.println(row > 0 ? "删除成功" : "删除失败");
		
		Student student = impStudent.selectOneStudentBy(8);
		System.out.println("查询单个对象"+student);
	
		
		List name = impStudent.selectAllStudentName();
		for (Object string : name) {
			System.out.println(string);
		}
		
		Map<String, Object> map = impStudent.selectOneStudentNameBy(8);
		System.out.println(map.get("name")+" "+ map.get("gender"));
		
		
		
		
	}

}

qq_39095899

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
JDBC(三)

JDBC（三）实际开发中"连接数据库"或者"断开数据库"比较消耗资源,为了提高程序的性能,要使用数据库连接池,共享连接(connection)连接池的方式:1.DBCP:tomcat内置的连接池2.C3P0:SSH中内置的连接池3.Druid:阿里开源的连接池三种连接池的Util包的配置都会有的导入jar包...
复制链接

扫一扫