Java与数据库

一.JDBC数据库连接

JDBC:Java DataBase Connectivity java的数据库连接技术

1.JDBC的使用步骤

(1)Eclipse的环境配置
	Ⅰ.到官网http://dev.mysql.com/downloads/connector/下载mysql的驱动包
	Ⅱ.选择Connector/J，然后再先选择自己需要的版本进行下载
	Ⅲ.安装完成后到C:\Program Files (x86)\MySQL\Connector J 5.1
	   找文件mysql-connector-java-5.1.48-bin.jar
	Ⅳ.复制该文件到java项目的根目录下或者libs目录下，然后右击bulid path->add to bulid path

(2)使用流程
		//1.加载驱动
		DriverManager.registerDriver(new Driver());
		
		//2.获取连接
		Connection connection = DriverManager.getConnection("jdbc:mysql://主机名:端口号/数据库名","root","root");
		
		//3.执行增删改查
		//3-1编写sql语句
		String sql="delete from beauty where id=9";
		
		//3-2获取执行sql语句命令的对象
		Statement statement=connection.createStatement();
		
		//3-3使用命令对象执行sql语句
		int update=statement.executeUpdate(sql);//返回受影响的行数
		
		//3-4处理执行结果
		System.out.println(update>0?"success":"failure");
		
		//4.关闭连接，先用的后关，后用的先关
		statement.close();
		connection.close();		

(3)加载驱动
	/*
	 * 会导致类加载的情况：
	 * 1.new 类的对象时(编译期加载)
	 * 2.加载子类，父类会被加载
	 * 3.调用了类中的静态成员
	 * 4.通过反射
	 * 
	 * 
	 * 使用之前DriverManager.deregisterDriver(new Driver());的方式加载类的不足
	 * 1.对该类的包依赖性太强
	 * 2.会导致Driver对象创建了两遍，从源码中可以看到，在静态代码块中已经创建一次了
	 * 
	 * 所以一般我们使用反射的方式
	 * 1.属于运行期加载，降低了类的依赖性
	 * 2.Driver对象仅仅创建了一遍，效率较高
	 */
	 	//老方式
		DriverManager.deregisterDriver(new Driver());
		
		//新方式：反射,这样在加载Driver类的时候，就会常见一个Driver对象
		Class.forName("com.mysql.jdbc.Driver");

(4)获取连接
	/*老方法
	 *1注册驱动
	 *Class.forName("com.mysql.jdbc.Driver");
	 *
	 *2.获取连接
	 *DriverManager.getConnection("jdbc:mysql://主机名:端口号/库名","账号","密码");
	 */
		
	//新方法(推荐)DriverManager.getConnection("jdbc:mysql://主机名:端口号/库名", .properties配置文件);
	Properties info = new Properties();
	info.load(new FileInputStream("src\\jdbc.properties"));
	String user = info.getProperty("user");
	String password = info.getProperty("password");
	String driver = info.getProperty("driver");
	String url = info.getProperty("url");
		
	//1.注册驱动
	Class.forName(driver);
		
	//2.获取连接
	Connection connect = DriverManager.getConnection(url,user,password);

(5)执行增删改查
	//3.执行增删改查
	String sql = "select id,name,sex,borndate from beauty";
	//获取执行sql的命令对象
	Statement statement = connection.createStatement();
	//执行sql
	int update = statement.executeUpdate(sql);//执行增删改语句，返回受影响的行数
	ResultSet set = statement.executeQuery(sql);//执行查询语句，返回的是结果集，内有一个迭代器
		
	//获取结果集中的字段
	//方式一：get类型(列的索引)
	set.getInt(1);
	set.getString(2);
	set.getString(3);
	set.getDate(4);
		
	//方式二：get类型(字段名)
	set.getInt("id");
	set.getString("name");
	set.getString("sex");
	set.getDate("borndate");
		
	while(set.next()){
		int id = set.getInt(1);
		String name = set.getString(2);
		String sex = set.getString(3);
		Date date = set.getDate(4);
	}

2.PreparedStatement和Statement的区别

(1)Statement能够创建sql的命令对象，并且有一些执行sql语句的方法

(2)PreparedStatement继承于Statement，并做了一些改进

(3)登录验证案例(Statement实现)
	//使用Statement实现登录
	@Test
	public void testStatement() throws Exception{
		Scanner input = new Scanner(System.in);
		System.out.println("Please input username:");
		String username = input.next();
		System.out.println("Please input password:");
		String pwd = input.next();
		
		//--------------------以下为连接数据库的步骤-----------------
		Properties info = new Properties();
		info.load(new FileInputStream("src\\jdbc.properties"));
		String user = info.getProperty("user");
		String password = info.getProperty("password");
		String driver = info.getProperty("driver");
		String url = info.getProperty("url");
		
		//1.注册驱动
		Class.forName(driver);
		//2.获取连接
		Connection connection = DriverManager.getConnection(url,user,password);
		//3.执行查询
		String sql = "select count(*) from acmin where username = '"+username+"' and password='"+pwd+"'";
		
		//创建sql命令对象
		Statement statement = connection.createStatement();
		//执行sql语句
		ResultSet set = statement.executeQuery(sql);
		
		if(set.next()){
			int count = set.getInt(1);
			System.out.println(count>0?"Login Success":"Login Failure");
		}
		
		//4.关闭
		set.close();
		statement.close();
		connection.close();
		
(4)登录验证案例(PreparedStatement实现)
		//使用PreparedStatement实现登录
		@Test
		public void testPreparedStatement() throws Exception{
			Scanner input = new Scanner(System.in);
			System.out.println("Please input username:");
			String username = input.next();
			System.out.println("Please input password:");
			String pwd = input.next();
			
			//--------------------以下为连接数据库的步骤-----------------
			Properties info = new Properties();
			info.load(new FileInputStream("src\\jdbc.properties"));
			String user = info.getProperty("user");
			String password = info.getProperty("password");
			String driver = info.getProperty("driver");
			String url = info.getProperty("url");
			
			//1.注册驱动
			Class.forName(driver);
			//2.获取连接
			Connection connection = DriverManager.getConnection(url,user,password);
			//3.执行查询
			//3-1编写sql语句
			String sql = "select count(*) from admin where username =? and password=?";
			//3-2获取PreparedStatement命令对象
			PreparedStatement statement = connection.prepareStatement(sql);
			
			//3-3设置占位符?的值
			statement.setString(1, username);//把第一个？占位符设置成username
			statement.setString(2, pwd);
			
			//3-4执行sql命令
			if(set.next()){
				int count = set.getInt(1);
				System.out.println(count>0?"Login Success":"Login Failure");
			}
			
			//4.关闭
			set.close();
			statement.close();
			connection.close();
	}

(5)PreparedStatement的好处
	不再使用+拼接sql语句，减少语法错误，语义性强
	将模板sql(固定部分)和参数部分进行了分离，提高了维护性
	有效的解决了sql的注入的问题
	效率高(预编译,减少了编译次数)

3.JDBC的相关API

(1)DriverManager驱动管理类
	registDriver(Driver对象):注册驱动连接(不推荐使用)
	getConnection(url,user,pwd):获取连接
	
(2)Connection连接对象接口
	createStatement():生成命令对象
	prepareStatement(sql):生成预编译命令对象

(3)Statement命令对象接口
	executeUpdate(sql):执行增删改语句，返回受影响的行数
	executeQuery(sql):执行查询语句，返回结果集
	execute(sql):执行任意语句，返回boolean

(4)PreparedStatement预编译命令对象接口
	executeUpdate():执行增删改语句，返回受影响的行数
	executeQuery():执行查询语句，返回结果集
	execute():执行任意sql语句，返回boolean
	setXX(占位符索引,占位符的值):设置对应索引的占位符的值，类型为XX类型
	setObject(占位符索引,占位符的值):设置对应索引的占位符的值，类型为Object类型

(5)ResultSet结果集对象接口(占位符索引从1开始)
	next():下移一行，返回当前行是否有值
	previous():上移一行，返回当前行是否有值
	getXX(列索引|列名|别名):返回对应列的值，接收类型为XX
	getObject(列索引|列名|别名):返回对应列的值，接收类型为Object

4.封装成JDBCUtils

(1)因为JDBC每次连接都需要重复那4个步骤，我们可以创建类JDBCUtils，把JDBC封装到里面

(2)JDBCUtils的功能
	Ⅰ.获取连接
	Ⅱ.释放资源

(3)实现
public class JDBCUtils {
	static String user;
	static String password;
	static String url;
	static String driver;
	
	static{
		Properties info = new Properties();
		try {
			info.load(new FileInputStream("src\\jdbc.properties"));
		} catch (Exception e1) {
			e1.printStackTrace();
		}
		
		user = info.getProperty("user");
		password = info.getProperty("password");
		url = info.getProperty("url");
		driver = info.getProperty("driver");
		
		//1.注册驱动
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
	}
	
	//获取连接
	public static Connection getConnection() throws Exception{
		//2.获取连接
		Connection connection = DriverManager.getConnection(url,user,password);
		
		return connection;
	}
	
	//释放资源
	public static void close(ResultSet set,Statement statement,Connection connection) throws Exception{
		if(set!=null){
			set.close();
		}
		if(statement!=null){
			statement.close();
		}
		if(connection!=null){
			connection.close();
		}
		
	}

}
之后建立连接和关闭连接的时候就可以直接调用getConnection()和close()方法即可

5.JDBC中的事务

(1)使用步骤
	Ⅰ.开启新事务
		取消事务的自动提交的功能
			setAutoCommit(false);
			
	Ⅱ.编写组成事务的一组sql语句
	
	Ⅲ.结束事务
	commit():提交
	rollback():回滚

	Ⅳ.注
		开启事务的连接对象和获取命令的连接对象必须是同一个，否则事务无效

(2)案例：转账(不使用事务)
	public void testNoTransaction() throws Exception{
		//1.获取连接
		Connection connection = JDBCUtils.getConnection();
		
		//2.执行sql语句
		PreparedStatement statement = connection.prepareStatement("update account set balance=? where stuname=?");
		
		//执行操作1：张三丰的钱-5000
		statement.setDouble(1, 5000);
		statement.setString(2, "张三丰");
		statement.executeUpdate();
		
		//异常
		int i = 1/0;
		
		//执行操作2：灭绝的钱+5000
		statement.setDouble(1,15000);
		statement.setString(2,"灭绝师太");
		statement.executeUpdate();
		
		//3.释放资源
		JDBCUtils.close(null, statement, connection);
	}
会导致张三丰的钱减了，灭绝师太的钱没增

(3)案例：转账(使用事务)
	public void testNoTransaction(){
		Connection connection = null;
		PreparedStatement statement = null;
		try{
			//1.获取连接
			connection = JDBCUtils.getConnection();
			
			//步骤1：开启事务(取消语句的自动提交)
			connection.setAutoCommit(false);
			
			//步骤2：编写事务的sql语句并执行
			statement = connection.prepareStatement("update account set balance=? where stuname=?");
			
			//张三丰的钱-5000
			statement.setDouble(1, 5000);
			statement.setString(2, "张三丰");
			statement.executeUpdate();
			
			//灭绝的钱+5000
			statement.setDouble(1,15000);
			statement.setString(2,"灭绝师太");
			statement.executeUpdate();
			
			//步骤3：无异常，结束事务
			connection.commit();
				
		}catch(Exception e){
			try {
				//出现异常，回滚
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}finally{
			JDBCUtils.close(null, statement, connection);
		}
	}

6.JDBC中的批处理

(1)适用情况
	当需要成批的插入或更新记录时，可以采用java的批量更新机制
	Ⅰ.多条无关的sql语句批处理(只能一条一条sql语句去加)
		Statement st = connection.createStatement();
		st.addBatch(sql1);
		...
		st.executeBatch();
		
	Ⅱ.sql语句的批量传参
		PreparedStatement pst = connection.prepareStatement(sql);
		for(int i = 100;i<1101;i++){
			pst.setInt(1,i);
			...
			pst.addBatch();
		}
		pst.executeBatch();
		...

(2)批处理用到的API
	addBatch():添加需要批处理的SQL语句或参数
	executeBatch():执行批量处理语句
	clearBatch():清空批处理缓存中的语句

(3)注意
	Ⅰ.如果想要使用批处理功能，需要在url参数中添加参数
		?rewriteBatchedStatements=true
	开启批处理功能，这个改一下jdbc.properties配置文件即可
	
	Ⅱ.使用PreparedStatement作批处理时插入时使用values(使用value没有效果)

(4)案例：向admin表中插入50000行数据(未用批处理的情况)
	@Test
	public void testNoBatch() throws Exception{
		//1.获取连接
		Connection connection = JDBCUtils.getConnection();
		
		//2.执行插入
		PreparedStatement statement = connection.prepareStatement("insert into admin values(null,?,?)");
		for(int i = 0;i<500000;i++){
			statement.setString(1, "john"+i);
			statement.setString(2, "0000");
			
			statement.executeUpdate();//执行	
		}
		
		//3.释放资源
		JDBCUtils.close(null, statement, connection);
	}

(5)案例：向admin表中插入50000行数据(使用批处理的情况)
	@Test
	public void testBatch() throws Exception{
		//1.获取连接
		Connection connection = JDBCUtils.getConnection();
			
		//2.执行插入
		PreparedStatement statement = connection.prepareStatement("insert into admin values(null,?,?)");
		for(int i = 0;i<500000;i++){
			statement.setString(1, "john"+i);
			statement.setString(2, "0000");
				
			statement.addBatch();//添加sql语句到批处理包中
			if(i%1000==0){
				statement.executeBatch();//执行批处理缓存中的sql语句
				statement.clearBatch();//清空批注缓存中的sql语句
			}
		}
			
		//3.释放资源
		JDBCUtils.close(null, statement, connection);
	}

(6)说明
	批处理往往和PreparedStatement一起搭配使用，既减少了编译次数，又减少了运行次数

7.Blob(二进制文件)类型数据的读写(选学)

(1)以IO流的方式读写

(2)存图片
	@Test
	public void testSave() throws Exception{
		//1.获取连接
		Connection connection = JDBCUtils.getConnection();
		
		//2.执行修改语句
		PreparedStatement statement = connection.prepareStatement("update beauty set photo=? where id=1");
		
		statement.setBlob(1, new FileInputStream("src\\6.jpg"));
		int update = statement.executeUpdate();
		
		//3.关闭连接
		JDBCUtils.close(null, statement, connection);
	}

(3)读图片
	@Test
	public void testRead() throws Exception{
		//1.获取连接
		Connection connection = JDBCUtils.getConnection();
		
		//2.执行修改语句
		PreparedStatement statement = connection.prepareStatement("update beauty set photo=? where id=1");
		ResultSet set = statement.executeQuery();
		if(set.next()){
			InputStream inputStream = set.getBinaryStream("photo");
			
			FileOutputStream fos = new FileOutputStream("e:\\demo");
		}
		
		//3.关闭连接
		JDBCUtils.close(null, statement, connection);
	}

8.Druid数据库连接池

(1)为什么需要数据库连接池
	我们之前使用JDBC每次都需要进行连接的建立和连接的释放，这样不仅耗费时间，也浪费数据库资源
	因此我们可以使用数据库连接池，类似于共享单车，先在缓冲池中放入一定数量的连接，有谁需要就将池中已经建好的连接分配给他，使用完毕再归还
	连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，如果池中没有连接了，请求会被加入等待队列中

(2)Druid(德鲁伊)数据库连接池
	阿里开发的开源数据库连接池

(3)环境配置
	和mysql的驱动差不多，也是下好之后找到
		druid-1.0.9.jar文件
	复制到项目根目录或者lib目录下，然后添加构建路径

(4)使用步骤
	编写druid.properties文件，创建数据库连接池

(5)Druid.properties文件
	#key=value
	driverClassName=com.mysql.jdbc.Driver
	url=jdbc:mysql://localhost:3306/myemploeese?rewriteBatchedStatements=true
	#url=jdbc:mysql://localhost:3306/myemploeese
	username=root
	password=123456
	initialSize=10		#缓冲池默认的连接对象数
	minInle=5			#最少的空闲连接数
	maxActive=20		#最大连接数
	maxWait=5000		#连接最长不能超过5000毫秒

(6)创建数据库代码块的演示代码
	@Test
	public void testDataSource() throws Exception{
		Properties properties = new Properties();
		properties.load(new FileInputStream("src\\druid.properties"));
		
		//1.创建了一个指定参数的数据库连接池
		DataSource ds = DruidDataSourceFactory.createDataSource(properties);
		
		//2.从数据库连接池中获取可用的连接对象
		Connection connection = ds.getConnection();
		...//照常使用
	}

(7)可以把上面的这些写进JDBCUtilsByDruid中
变为：
	public class JDBCUtilsByDruid {
	static DataSource ds;
	static{
		try{
			Properties properties = new Properties();
			properties.load(new FileInputStream("src\\druid.properties"));
			
			//1.创建一个指定参数的数据库连接池
			//因为在静态代码块中，所以这段代码只会执行一次
			ds = DruidDataSourceFactory.createDataSource(properties);
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	public static Connection getConnection() throws Exception{
		//2.从数据库连接池中获取可用的连接对象
		return ds.getConnection();
	}
	
	//释放资源
	public static void close(ResultSet set,Statement statement,Connection connection) throws Exception{
		if(set!=null){
			set.close();
		}
		if(statement!=null){
			statement.close();
		}
		if(connection!=null){
			connection.close();
		}
	}	
}
这样以后调用getConnection()方法就直接创建的连接池，且代码更简洁

二.DBUtils工具类库

(1)这个也是第三方的开源框架，环境配置和之前一样
	到http://commons.apache.org/proper/commons-dbutils/download_dbutils.cgi
	下载commons-dbutils-1.7-bin.zip
	找到文件commons-dbutils-1.7.jar
	添加到构建路径中即可

(2)测试update()方法(增删改)
	@Test
	public void testUpdate() throws Exception{
		//1.获取连接
		Connection connection = JDBCUtilsByDruid.getConnection();
			
		//2.执行增删改
		QueryRunner qr = new QueryRunner();
		int update = qr.update(connection,"update boys set boyname=? where id=4","慕容");
			
		System.out.print(update>0?"success":"failure");
			
		//3.关闭连接
		JDBCUtilsByDruid.close(null, null, connection);
	}

(3)测试query()方法(把第一行的值转换为类对象)
	@Test
	public void testQuerySingle() throws Exception{
		//1.获取连接
		Connection connection = JDBCUtilsByDruid.getConnection();
			
		//2.执行增删改
		QueryRunner qr = new QueryRunner();
			
		类名 对象名 = qr.query(connection, "sql语句",new BeanHandler<>(类名.class),占位符的值);
		//想把查询出来的结果转换为什么类就改一下
		//但是类中定义的属性名要和字段名一致，且需要有无参构造器
		//如果返回的是多行 把方法改成BeanListHandler()即可
			
		//3.关闭连接
		JDBCUtilsByDruid.close(null, null, connection);
	}

(3)所用API
	QueryRunner类：
		update(connection,sql,params):执行任何增删改语句
		query(connection,sql,ResultSetHandler,params):执行任何查询语句
	
	ResultSetHandler接口：
		BeanHandler():将结果集的第一行，封装成对象并返回				new BeanHandler<>(xx.class);
		BeanListHandler:将结果集中的所有行，封装成对象的集合并返回		new BeanListHandler<>(xx.class);
		ScalarHandler:将结果集中的第一行第一列，以Object形式放回		new ScalarHandler();