JDBC简单工作原理

一.JDBC工作过程（搜索mysql导入5.1.37jar版本的包）
1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
2.建立连接（Connection接口）——五个参数，地址，端口号，要使用的库名，用户名，密码
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql","root","");

3.获取语句对象并执行SQL语句（Statement接口）
Statement sta = conn.createStatement();
boolean flag = sta.execute(ddl);
int n = sta.executeUpdate(dml);

4.处理结果集（ResultSet接口）
ResultSet rs = sta.executeQuery(dql);
boolean flag = rs.next();
String str = rs.getString("name");
String name = rs.getString(2);
5.关闭连接
conn.close();

二.数据库配置写在一个resources下的.properties属性文件里
1.获得properties对象（实现了map接口）
Properties cfg = new Properties();

2.获取db.properties文件的流
InputStream in = Demo5.class.getClassLoader().getResourceAsStream("db.properties");

3.调用load方法
cfg.load(in);

4.通过cfg.getProperty(key)获得值
String driver = cfg.getProperty("jdbc.driver");

三.连接池技术（DataBase connection Pool）——搜索dbcp导入1.4jar版本的包
1.创建一个连接池对象
BasicDataSource bds = new BasicDataSource();
2.设置连接池参数
（1）必选参数
bds.setDriverClassName(driver);
bds.setUrl(url);
bds.setUsername(username);
bds.setPassword(password);
（2）连接池的管理策略参数
//初始化连接数
bds.setInitialSize(2);
//最大连接数
bds.setMaxActive(2);
3.建立连接，处理结果集
Connection con = bds.getConnection();
Statement sta = con.createStatement();
ResultSet rs = sta.executeQuery(dql);
4.用户关闭连接，连接归还给连接池
con.close();

四.升级版Statement接口PreparedStatement接口的用法
1.获取语句对象
String sql = "insert into dept values(?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);

2.设置具体值
ps.setInt(1, 1);
ps.setString(2, "linsa");
ps.setString(3, "123");

3.执行SQL语句
int n=ps.executeUpdate();

JDBC工作过程Demo1:

package day01;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Demo1 {
	public static void main(String[] args) throws Exception {
		//1.注册驱动
			//1.1导包
			//1.2注册驱动
		Class.forName("com.mysql.jdbc.Driver");
		System.out.println("ok");
		//2.创建连接对象
		String url="jdbc:mysql://localhost:3306/mysql";
		String username="root";
		String password="";
		Connection con = DriverManager.getConnection(url,username,password);
		System.out.println(con);
		//3.获取语句对象（操作sql语句）
			//3.1拼写sql语句
		String ddl = "create table dept (id int,name varchar(20),password varchar(20))";
		System.out.println(ddl);
			//3.2获得语句对象
		Statement sta = con.createStatement();
			//3.3执行sql语句
//		sta.execute(sql);---ddl,dcl
			//返回值
			//返回了结果集---true
			//返回了int值---false
			//抛异常，说明执行失败
//		sta.executeUpdate(sql);---dml
//		sta.executeQuery(sql);---dql
		boolean flag = sta.execute(ddl);
		System.out.println(flag);
		String dml = "insert into dept values(1,'linsa','123')";
		int n = sta.executeUpdate(dml);
		System.out.println(n);
		String dql = "select name as u,password as p from dept where id=1";
		ResultSet rs = sta.executeQuery(dql);
		System.out.println(rs);
		//ResultSet一出现，就上while
		//4.处理结果集
		while(rs.next()) {
			//sql语句中，有了别名，那么原列名就没有了，用别名
			String str = rs.getString("u");
			//如果使用下标，从1开始
			String str1 = rs.getString(2);
			System.out.println(str);
			System.out.println(str1);
		}
		//5.关闭连接
		con.close();
	}
}

数据库配置文件Demo2：

配置文件db.properties

#properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mysql?useUnicode=true&characterEncoding=utf8
jdbc.username=root
jdbc.password=

package day01;

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

public class Demo2 {
	public static void main(String[] args) throws IOException {
		//1.获得properties对象
		Properties cfg = new Properties();
		//2.获取db.properties文件的流
		InputStream in = Demo5.class.getClassLoader().getResourceAsStream("db.properties");
		System.out.println(in);
		//3.调用load方法
		cfg.load(in);
		//4.通过cfg.getProperty(key)获得值
		String driver = cfg.getProperty("jdbc.driver");
		String url = cfg.getProperty("jdbc.url");
		String username = cfg.getProperty("jdbc.username");
		String password = cfg.getProperty("jdbc.password");
		System.out.println(driver);
		System.out.println(url);
		System.out.println(username);
		System.out.println(password);
	}
}

连接池技术Demo3：

package day01;

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

import org.apache.commons.dbcp.BasicDataSource;

public class Demo3 {
	//在这个测试类中，测试使用连接池获得conn对象
	//测试连接池的连接上限
	public static void main(String[] args) throws SQLException {
		String driver = "com.mysql.jdbc.Driver";
		String url="jdbc:mysql://localhost:3306/mysql";
		String username="root";
		String password="";
		//创建一个连接池对象
		BasicDataSource bds = new BasicDataSource();
		//连接池的必选参数
		bds.setDriverClassName(driver);
		bds.setUrl(url);
		bds.setUsername(username);
		bds.setPassword(password);
		//连接池的管理策略参数
		//初始化连接数
		bds.setInitialSize(2);
		//最大连接数
		bds.setMaxActive(2);
		Connection con = bds.getConnection();
		Statement sta = con.createStatement();
		String dql = "select*from dept";
		ResultSet rs = sta.executeQuery(dql);
		while(rs.next()) {
			System.out.println(rs.getString(2));
		}
		con.close();
	}
}

PreparedStatement接口的用法：

配置文件db.properties


#properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mysql
jdbc.username=root
jdbc.password=
init=5
maxactive=5


封装类：

package day02;

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

import org.apache.commons.dbcp.BasicDataSource;

public class DBUtils {
	private static String driver;
	private static String url;
	private static String username;
	private static String password;
	private static int init;
	private static int maxactive;
	private static BasicDataSource bds=null;
	static {
		bds=new BasicDataSource();
		Properties cfg=new Properties();
		InputStream inStream=DBUtils.class
				.getClassLoader()
				.getResourceAsStream("db.properties");
		try {
			cfg.load(inStream);
			driver=cfg.getProperty("jdbc.driver");
			url=cfg.getProperty("jdbc.url");
			username=cfg.getProperty("jdbc.username");
			password=cfg.getProperty("jdbc.password");
			init=Integer.parseInt(cfg.getProperty("init"));
			maxactive=Integer.parseInt(cfg.getProperty("maxactive"));
			
			//设置BasicDataSource必要参数
			bds.setDriverClassName(driver);
			bds.setUrl(url);
			bds.setUsername(username);
			bds.setPassword(password);
			//设置BasicDataSource管理策略参数
			bds.setInitialSize(init);
			bds.setMaxActive(maxactive);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	public static Connection getConnection() {
		Connection conn=null;
		try {
			conn=bds.getConnection();
			return conn;
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}
	public static void closeConnection(Connection conn) {
		if (conn!=null) {
			try {
				//这里的close不是关闭的意思
				//归还
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

Test1：

package day02;

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

public class Test1 {
	public static void main(String[] args) {
		String sql="select name from dept "
				+ "where name like ?";
		Connection conn=null;
		try {
			conn=DBUtils.getConnection();
			PreparedStatement ps =conn.prepareStatement(sql);
			
			ps.setString(1, "%s%");
			ResultSet rs=ps.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getString(1));
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtils.closeConnection(conn);
		}
	}
}

接口Statement查密码可输入万能钥匙（用户名随便输入，密码为：'or '1'='1）
用接口PreparedStatement则不能使用万能钥匙

package day02;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class Test2 {
	public static void main(String[] args) {
		Scanner scan = new Scanner(System.in);
		System.out.println("请输入用户名：");
		String name = scan.nextLine();
		System.out.println("请输入密码：");
		String pwd = scan.nextLine();
		scan.close();
		boolean flag = login(name,pwd);
		if(flag) {
			System.out.println("查有此人");
		}else {
			System.out.println("查无此人");
		}
	}
	public static boolean login(String name,String pwd) {
		Connection conn = null;
		try {
			conn=DBUtils.getConnection();
			Statement sta = conn.createStatement();
			String sql = "select count(*) from dept where name='"+name+"' and password='"+pwd+"'";
			System.out.println(sql);
			ResultSet rs = sta.executeQuery(sql);
			while(rs.next()) {
				int n=rs.getInt(1);
				return n>=1;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtils.closeConnection(conn);
		}
		return false;
	}
}

多线程连接JDBCDemo6：

package day02;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class Test3 {
	public static void main(String[] args) {
		Thread t1=new DemoThread(5000, "连接1");
		Thread t2=new DemoThread(6000, "连接2");
		Thread t3=new DemoThread(7000, "连接3");
		t1.start();
		t2.start();
		t3.start();
	}
}
class DemoThread extends Thread{
	int wait;//睡的毫秒数
	String connName;//连接的名字
	//构造方法
	public DemoThread(int wait,String connName) {
		this.wait=wait;
		this.connName=connName;
	}
	public void run() {
		Connection conn = DBUtils.getConnection();
		System.out.println(connName+"   连接成功");
		try {
			String sql = "select 'hello' as a from dual";
			Statement sta=conn.createStatement();
			ResultSet rs = sta.executeQuery(sql);
			while(rs.next()) {
				System.out.println(connName+"       "+rs.getString(1));
			}
			Thread.sleep(wait);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBUtils.closeConnection(conn);
		}
	}
}