JDBC(三)

news/2024/5/19 0:21:26 标签: mysql, 数据库, jdbc, java

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"));
		
		
		
		
	}

}

http://www.niftyadmin.cn/n/899466.html

相关文章

java学习心得---循环结构

java学习心得---循环结构

java学习心得—循环结构 通过对java循环操作的学习&#xff0c;让我对java这门语言有了更深层次的了解。下面我总结一下今天学习的内容&#xff1a; 一、什么是循环结构&#xff1f; 循环结构是通过某个条件&#xff0c;重复的执行一段逻辑代码。 二、循环结构包括什么&am…
阅读更多...
JavaScript之ECMAScript

JavaScript之ECMAScript

ECMAScript 1.JavaScript是运行在浏览器上的脚本语言。简称JS.;小故事在文章最后&#xff0c;先上硬菜 2.js是一门事件驱动型的编程语言&#xff0c;依靠事件去驱动&#xff0c;然后执行对应的程序。 在js中有很多事件&#xff0c;其中有一个事件叫做:鼠标单击&#xff0c;单词…
阅读更多...
JavaScript之DOM

JavaScript之DOM

DOM DOM&#xff1a;Document Object Model(文档对象模型&#xff0c;对网页当中的节点进行增删改的过程。)HTML文档被当作一棵Dom树来看待 获取文本框value <script type"text/javascript">/*window.onload function(){//var btnObj window.document.getE…
阅读更多...
java学习心得---方法

java学习心得---方法

java学习心得—方法 一、概念 方法是实现特定功能的一段代码&#xff0c;可以重复使用。 二、定义 一些列代码指令的合集&#xff08;就是将一些代码整合再一起&#xff09;。 三、作用 提高代码的重复性。 四、定义的格式 访问修饰符返回值类型方法名称&#xff08;这…
阅读更多...
java作业（数组）

java作业（数组）

答题 第一题 A只声明了数组变量 B声明了数组变量&#xff0c;赋值长度。 C应该为int[]a{1,2,3,4,5}; D错误 第二题 第三题 第四题 第五题 第六题
阅读更多...
JavaScript之BOM编程

JavaScript之BOM编程

BOM编程 什么是BOM? Brower Object Model(浏览器对象模型&#xff0c;)关闭浏览器窗口、打开一个新的浏览器窗口、后退、前进、浏览器地址栏上的地址等&#xff0c;都是BOM编程 BOM和DOM的区别与联系&#xff1f; BOM的顶级对象是&#xff1a;window DOM的顶级对象是&#xf…
阅读更多...
JQuery基础练习

JQuery基础练习

Jquery练习 基本选择器练习 <!DOCTYPE html> <html><head><meta charset"UTF-8"><title>jquery基本选择器</title><script src"js/jquery-3.4.1.js" type"text/javascript" charset"utf-8"&…
阅读更多...
java学习0302作业

java学习0302作业

1.目前我们学过的循环有哪些&#xff1f;有什么区别 答&#xff1a;目前学习了while、do-while和for循环 其中while循环和for循环都是先判断&#xff0c;再循环。就是满足循环条件才会循环。但是for循环相比while循环来说写法更为简洁。 do-while循环是先循环&#xff0c;再判断…
阅读更多...
最新文章

Copyright @ 2022~2023