Java数据库开发与实战运用---JDBC

	public void demo() {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 1.加载驱动
//			DriverManager.registerDriver(new Driver());// 会导致驱动注册两次。
			Class.forName("com.mysql.jdbc.Driver");
			// 2.获得连接
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest", "root", "root");
			// 3.创建执行SQL语句的对象，并且执行SQL
			// 3.1创建执行sql的对象
			String sql = "select * from user";
			stmt = conn.createStatement();
			// 3.2执行sql
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				int uid = rs.getInt("uid");
				String username = rs.getString("username");
				String password = rs.getString("password");
				String name = rs.getString("name");

				System.out.println(uid + "   " + username + "   " + password + "   " + name);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 4.释放资源
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException sqlEx) { // ignore

				}

				rs = null;
			}

			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;// 垃圾回收机制更早回收对象。
			}
		}
	}

DriverManage :驱动管理类

主要作用：

一、注册驱动

实际开发中注册驱动会使用如下的方式：

Class.forName("com.mysql.jdbc.Driver");

用为之前的方式会导致驱动注册两次。

二、获得连接

Connection getConnection(String url,String username,String password);

url写法：jdbc:mysql://localhost:3306/jdbc

简写：jdbc:mysql:///jdbc

 

connection：连接对象

主要作用：

一、创建执行SQL语句的对象

• statement createStatement() ：执行sql语句，有sql注入的风险
• PrepareStatement prepareStrtement(String sql)：预编译SQL语句，解决SQL注入的漏洞
• CallableStatement prepareCall(String sql)：执行SQL中存储过程

二、进行事务管理

• setAutoCommit(boolean autoCommit)：设置事务是否自动提交
• commit：事务提交
• rollback()：事务回滚

Statement:执行SQL

主要作用：

一、执行SQL语句

• boolean execute(String sql):执行SQL，执行select语句返回true,否则返回false
• ResultSet executeQuery(String sql):执行SQL中的select语句
• int executeUpadate(String sql):执行SQL中的insert/update/delete语句

二、执行批处理操作

• addBatch(String sql):添加到批处理
• executeBatch():执行批处理
• clearBatch():清空批处理

ResultSet：结果集

结果集：其实就是查询语句（select）语句查询的结果的封装

主要作用：

结果集获取查询到的结果的。

next()：针对不同类型的数据可以使用getXXX()获取数据，通用的获取数据的方法：getObject();

JDBC的CRUD操作

保存操作

/**
	 * 保存操作
	 */
	public void demo1(){
		Connection conn = null;
		Statement stmt = null;
		try{
			// 注册驱动:
			Class.forName("com.mysql.jdbc.Driver");
			// 获得连接:
			conn = DriverManager.getConnection("jdbc:mysql:///jdbctest", "root", "abc");
			// 获得执行SQL语句的对象:
			stmt = conn.createStatement();
			// 编写SQL：
			String sql = "insert into user values (null,'eee','123','张三')";
			// 执行SQL:
			int i = stmt.executeUpdate(sql);
			if(i > 0){
				System.out.println("保存成功！");
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			// 释放资源:
			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 void demo2(){
		Connection conn = null;
		Statement stmt = null;
		try{
			// 注册驱动
			Class.forName("com.mysql.jdbc.Driver");
			// 获得连接
			conn = DriverManager.getConnection("jdbc:mysql:///jdbctest", "root", "abc");
			// 创建执行SQL语句的对象:
			stmt = conn.createStatement();
			// 编写SQL:
			String sql = "update user set username = 'qqq',password='456' , name='赵六' where uid = 4";
			// 执行SQL:
			int i = stmt.executeUpdate(sql);
			if(i>0){
				System.out.println("修改成功!");
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			// 释放资源
			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 void demo3(){
		Connection conn = null;
		Statement stmt = null;
		try{
			// 注册驱动
			Class.forName("com.mysql.jdbc.Driver");
			// 获得连接
			conn = DriverManager.getConnection("jdbc:mysql:///jdbctest", "root", "abc");
			// 获得执行SQL语句的对象:
			stmt = conn.createStatement();
			// 编写SQL:
			String sql = "delete from user where uid = 4";
			// 执行SQL:
			int i = stmt.executeUpdate(sql);
			if(i > 0){
				System.out.println("删除成功!");
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			// 释放资源
			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 void demo4(){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			// 注册驱动:
			Class.forName("com.mysql.jdbc.Driver");
			// 获得连接:
			conn = DriverManager.getConnection("jdbc:mysql:///jdbctest", "root", "abc");
			// 创建执行SQL语句的对象:
			stmt = conn.createStatement();
			// 编写SQL:
			String sql = "select * from user";
			// 执行SQL:
			rs = stmt.executeQuery(sql);
			// 遍历结果集:
			while(rs.next()){
				System.out.println(rs.getInt("uid")+"   "+rs.getString("username")+"   "+rs.getString("password")+"    "+rs.getString("name"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			// 释放资源
			if(rs != null){
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				rs = null;
			}
			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 void demo5(){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			// 注册驱动
			Class.forName("com.mysql.jdbc.Driver");
			// 获得连接
			conn = DriverManager.getConnection("jdbc:mysql:///jdbctest", "root", "abc");
			// 创建执行SQL语句的对象
			stmt = conn.createStatement();
			// 编写SQL
			String sql = "select * from user where uid = 1";
			// 执行SQL
			rs = stmt.executeQuery(sql);
			if(rs.next()){
				System.out.println(rs.getInt("uid")+"   "+rs.getString("username")+"   "+rs.getString("password")+"   "+rs.getString("name"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			// 释放资源
			if(rs != null){
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				rs = null;
			}
			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;
			}			
		}
	}

JDBC的工具类的抽取

配置文件

package com.imooc.jdbc.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * JDBC的工具类
 * @author jt
 *
 */
public class JDBCUtils {
	private static final String driverClass;
	private static final String url;
	private static final String username;
	private static final String password;
	
	static{
		// 加载属性文件并解析：
		Properties props = new Properties();
		// 如何获得属性文件的输入流？
		// 通常情况下使用类的加载器的方式进行获取：
		InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
		try {
			props.load(is);
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		driverClass = props.getProperty("driverClass");
		url = props.getProperty("url");
		username = props.getProperty("username");
		password = props.getProperty("password");
	}

	/**
	 * 注册驱动的方法
	 * @throws ClassNotFoundException 
	 */
	public static void loadDriver() throws ClassNotFoundException{
		Class.forName(driverClass);
	}
	
	/**
	 * 获得连接的方法:
	 * @throws SQLException 
	 */
	public static Connection getConnection() throws Exception{
		loadDriver();
		Connection conn = DriverManager.getConnection(url, username, password);
		return conn;
	}
	
	/**
	 * 资源释放
	 */
	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(rs!= null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs = null;
		}
		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;
		}
	}
}

 

SQL注入漏洞

select * from user where username = 'aaa' or '1=1' and password = '1fsdsdfsdf'

select * from user where username = 'aaa' -- '1=1' and password = '1fsdsdfsdf'

sql注入漏洞的解决

 

C3P0连接池

导入jar包

配置c3o0-config.xml文件

工具类

package com.imooc.jdbc.utils;

import java.io.IOException;
import java.io.InputStream;
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 com.mchange.v2.c3p0.ComboPooledDataSource;

/**
 * JDBC的工具类
 * @author jt
 *
 */
public class JDBCUtils2 {
	private static final ComboPooledDataSource dataSource = new ComboPooledDataSource();
	
	/**
	 * 获得连接的方法:
	 * @throws SQLException 
	 */
	public static Connection getConnection() throws Exception{
		Connection conn = dataSource.getConnection();
		return conn;
	}
	
	/**
	 * 资源释放
	 */
	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(rs!= null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs = null;
		}
		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;
		}
	}
}