JDBC连接MySQL(干货)

JAVA连接JDBC操作数据库

什么是JDBC

Java DataBase Connectivity，Java数据库连接，jdbc实际是java中的一套和数据库交互的API(application program interface 应用程序编程接口)

为什么使用JDBC

因为Java程序员需要连接多种数据库，为了避免每一个数据库都学习一套新的API，sun公司提出了一个JDBC接口，各个数据库厂商根据此接口写实现类（驱动），这样java程序员只需要掌握JDBC接口中的一套方法，就可以访问任何数据库。
导包： import java.sql.*

如何使用JDBC

外网访问:https://mvnrepository.com/
1. 从maven网站上搜索mysql 找到5.1.6版本的坐标，把坐标复制到工程的pom.xml文件中 ，在pom.xml文件源码里面project标签内部 添加<dependecies>标签，把复制的内容粘贴到里面 ，保存在工程中出现奶瓶即可、
	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<version>5.1.6</version>
	</dependency>
2. 创建Demo01.java 在main方法中写以下代码：
	//1.注册驱动
	Class.forName("com.mysql.jdbc.Driver");
	//2. 获取连接对象
	Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/newdb3","root","root");
	//System.out.println(conn);
	//3.创建执行SQL语句的对象
	Statement stat = conn.createStatement();
	//4.执行SQL
	String sql = "create table if not exists jdbct1(id int primary key auto_increment,name varchar(10))";
	stat.execute(sql); 
	System.out.println("执行完成");
	//5.关闭资源,先打开的资源后关闭
	stat.close();
	conn.close(); 

执行SQL的方法

execute(sql): 此方法可以执行任意SQL，但是推荐执行DDL(create drop alter truncate) ,方法的返回值为布尔值,返回值代表是否有结果集，'true':有结果集，'false':没有结果集
executeUpdate(sql): (DML)此方法执行增删改的SQL，方法返回值为int类型，代表生效(ok)行数
executeQuery(sql):	此方法执行select查询的SQL，返回值为ResultSet结果集

解析配置文件

配置文件位置：
	src/main/resources
配置文件名：jdbc.properties
		driver=com.mysql.jdbc.Driver
		url=jdbc:mysql://localhost:3306/newdb3
		username=root
		password=123456
		initialSize=2
		maxActive=10

解析文件：

	/**
	 * 解析Properties后缀文件，获取对应的信息
	* properties后缀文件是以键值对方式存储信息
	 */
	//创建属性对象
	Properties prop= new Properties();
	//得到文件输入流'Demo05表示类名'
	InputStream ips=Demo05.class.getClassLoader().getResourceAsStream("jdbc.properties");
	//把流交给属性对象
	prop.load(ips);
	//获取数据,只能获取到字符串类型的数据
	String name = prop.getProperty("name");
	String age = prop.getProperty("age");
	System.out.println(name+": "+age);	
	//注册驱动以及设置连接池
ResultSet
	获取数据的方式有两种：
		1、通过查询到的字段名称获取数据   rs.getString("ename");
		2、通过查询到的字段位置获取数据   rs.getString(2);
	数据库类型和java类型对比：
		MySQL               	java
		int 		       		getInt()
		varchar		       		getString()
		float/double       		getFloat()/getDouble()
		datetime/timestamp 		getDate()

数据库连接池DBCP

DataBase Connection Pool:数据库连接池
	为什么使用：如果没有数据库连接池，每次业务都需要和数据库建立一次连接，用完之后断开，如果有1万次业务请求则有1万次连接的开和关，频繁开关连接，浪费资源，使用数据库连接池可以将连接重用，避免资源的浪费
如何使用数据库连接池
	1. 下载jar包： 从maven服务器中搜索 dbcp
		<dependency>
			<groupId>commons-dbcp</groupId>
			<artifactId>commons-dbcp</artifactId>
			<version>1.4</version>
		</dependency>
	2. 把坐标复制粘贴到pom.xml中
	3. 代码如下：
		//创建数据源对象
		BasicDataSource dataSource = new BasicDataSource();
		//设置数据库连接信息
		dataSource.setDriverClassName("com.mysql.jdbc.Driver");
		dataSource.setUrl("jdbc:mysql://localhost:3306/newdb3");
		dataSource.setUsername("root");
		dataSource.setPassword("123456");
		//设置初始连接数量
		dataSource.setInitialSize(3);
		//设置最大连接数量
		dataSource.setMaxActive(5);
		//从连接池中得到连接
		Connection conn = dataSource.getConnection();
		System.out.println(conn);

@Test 测试单元注解
只能对无参无返回值的方法进行单元测试

SQL的注入风险

select count(*) from t_user where username='libai' and password='admin1';
SQL注入的演示
	执行语句
		String sql = "insert into jdbcuser values(null,'"+name+"',"+age+")"; 
	输入：' or '1'='1	
	输出SQL拼接语句：
		select count(*) from t_user where username='asdfsdf' and password='' or '1'='1'
	出现登陆成功状态	

PreparedStatement对象

预编译的SQL对象
1. 为什么PreparedStatement可以避免SQL注入？
	因为预编译的SQL执行对象 在创建stat对象的时候已经将SQL进行编译并且把SQL语句的逻辑固定，不会因为后期替换进去的内容改变SQL语句		 	
	String sql = "select count(*) from t_user where username=? and password=?";	
	stat = conn.prepareStatement(sql);
	//替换问号的值
	stat.setString(1, username);
	stat.setString(2, password);	
	//执行SQL 
	rs = stat.executeQuery();		'不需要传入参数'	
2、好处
	1）. 代码更直观避免了拼接SQL语句 
	2）. 可以避免SQL注入 
	3）. 执行效率略高于statement
	如果SQL语句中存在变量则使用PreparedStatement，如果没有变量则使用Statement

批量操作

将多次和数据库服务器的数据交互合并成一次，提高执行效率
	'添加到批量操作中'
	stat.addBatch(sql1);
	stat.addBatch(sql2);
	stat.addBatch(sql3);
	'执行批量操作'
	stat.executeBatch();

事务

1、设置自动提交的状态 
	conn.setAutoCommit(false/true);
2、提交 
	conn.commit();
3、回滚
	conn.rollback();
	
案例：实现超人有5000  蝙蝠侠100 让超人给蝙蝠侠转账2000，程序第一次运行转账成功 第二次转账成功第三次转账失败 
	create table person(id int primary key auto_increment,name varchar(10), money int);
	insert into person values(null,'超人',5000),(null,'蝙蝠侠',100);
获取自增主键的值
	Statement
		stat.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
	PreparedStatement
		stat = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);	
	获取主键值		
		rs =stat.getGeneratedKeys();
		while(rs.next()){
			//得到返回的主键值
			int key=rs.getInt(1);
			System.out.println("自动生成的ID："+key);
		}

获取元数据

元数据包括：数据库的元数据和表的元数据 
1. 数据库元数据里面保存一些和数据库相关的信息 如:数据库厂商名称,数据库驱动版本,数据库连接地址,用户名等等
	//得到数据库的元数据
	DatabaseMetaData dbmd = conn.getMetaData();
	System.out.println("驱动版本:"+dbmd.getDriverVersion());
	System.out.println("连接地址:"+dbmd.getURL());
	System.out.println("用户名:"+dbmd.getUserName());
	System.out.println("数据库厂商:"+dbmd.getDatabaseProductName());
2. 表的元数据里面保存一些和表相关的信息 如：表有多少字段，每个字段的名称、类型等 
	//从结果集中得到表的元数据
	ResultSetMetaData rsmd = rs.getMetaData();
	//得到表字段的数量
	int count = rsmd.getColumnCount();
	//得到每个字段的字段名和类型
	for (int i = 0; i < count; i++) {
		String name = rsmd.getColumnName(i+1);
		String type = rsmd.getColumnTypeName(i+1);
		System.out.println(name+":"+type);
	}

JDBC练习源代码

JAV中连接JDBC需要导包：
	import java.sql.***;	'必须导入java.sql.下面的包，否则会出错'
1、注册驱动
	Class.forName("com.mysql.jdbc.Driver");
2、获取连接对象
	Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/newdb3","root", "123456"); 
3、创建执行SQL语句对象
	Statement stat=conn.createStatement();	
4、执行SQL
	String sql = "create table if not exists jdbct1(id int primary key auto_increment,name varchar(10))";
	stat.execute(sql);
	//查询表中的数据，见到ResultSet就是用while循环
	String sql="select * from emp";
	rs=stat.executeQuery(sql);
	while(rs.next()){
		int id=rs.getInt("empno");
		String name=rs.getString("ename");
		int sal=rs.getInt("sal");
		System.out.println("ID:"+id+"   姓名："+name+"   工资："+sal);
	}
5、关闭资源	
		stat.close();
		conn.close();
6、解析jdbc.properties配置文件'文件路径src/main/resources'
	解析Properties后缀文件，获取对应的信息,properties后缀文件是以键值对方式存储信息
	例如：
		driver=com.mysql.jdbc.Driver
		url=jdbc:mysql://localhost:3306/newdb3
		username=root
		password=123456
		//创建属性对象
		Properties prop= new Properties();
		//得到文件输入流,Demo05当前类名
		InputStream ips=Demo05.class.getClassLoader().getResourceAsStream("jdbc.properties");
		//把流交给属性对象
		prop.load(ips);
		//获取数据,只能获取到字符串类型的数据
		String username = prop.getProperty("username");
		String password = prop.getProperty("password");
		System.out.println(username+": "+password);		
7、创建数据库连接池
		//创建数据源对象
		BasicDataSource dataSource= new BasicDataSource();
		//设置数据库连接信息
		dataSource.setDriverClassName("com.mysql.jdbc.Driver");
		dataSource.setUrl("jdbc:mysql://localhost:3306/newdb3");
		dataSource.setUsername("root");
		dataSource.setPassword("123456");
		//设置初始连接数量
		dataSource.setInitialSize(3);
		//设置最大连接数量
		dataSource.setMaxActive(5);
		//从连接池中得到连接
		Connection conn= dataSource.getConnection();		
		System.out.println(conn);	
8、解析配置文件与连接池配合使用
	private static BasicDataSource dataSource;
	static{
		//创建属性对象
		Properties prop= new Properties();
		//得到文件输入流
		InputStream ips=Demo05.class.getClassLoader().getResourceAsStream("jdbc.properties");
		//把流交给属性对象
		try {
			prop.load(ips);
			//获取数据,只能获取到字符串类型的数据
			String driver = prop.getProperty("driver");
			String url = prop.getProperty("url");
			String username = prop.getProperty("username");
			String password = prop.getProperty("password");
			/**创建数据库连接池*/
			//创建数据源对象
			dataSource= new BasicDataSource();
			//设置数据库连接信息
			dataSource.setDriverClassName(driver);
			dataSource.setUrl(url);
			dataSource.setUsername(username);
			dataSource.setPassword(password);
			//设置初始连接数量
			dataSource.setInitialSize(3);
			//设置最大连接数量
			dataSource.setMaxActive(5);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}		
	//从连接池中得到连接
	public static Connection getConn() throws SQLException{			
		return dataSource.getConnection();
	}
	//关闭打开的资源
	public static void close(Connection conn,Statement stat,ResultSet rs){
		try {
			if(rs!=null){
				rs.close();				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(stat!=null){
				stat.close();				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(conn!=null){
				//保证自动提交处于打开状态,必要时会在程序中关闭自动提交
				conn.setAutoCommit(true); 	
				conn.close();			
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}	
9、连接池做测试
	public class demo02 {			
		public static void main(String[] args) throws SQLException {
			threadTest t1=new threadTest();
			t1.start();
			threadTest t2=new threadTest();
			t2.start();
			threadTest t3=new threadTest();
			t3.start();
			threadTest t4=new threadTest();
			t4.start();
			threadTest t5=new threadTest();
			t5.start();
		}
	}
	class threadTest extends Thread{
		@Override
		public void run(){
			//获取连接
			try {
				Connection conn = DBUtils.getConn();
				System.out.println("获取连接！");
				//模拟耗时
				Thread.sleep(5000);
				//归还连接
				conn.close();
				System.out.println("归还连接！");
			} catch (SQLException e) {
				e.printStackTrace();
			}
			catch(Exception e){
				e.printStackTrace();
			}
		}
	}
10、PreparedStatement使用测试
	public static void main(String[] args) {
		Scanner scan=new Scanner(System.in);
		System.out.println("请输入用户名：");
		String name = scan.nextLine();
		System.out.println("请输入年龄：");
		int age = Integer.parseInt(scan.nextLine());
		Connection conn = null;
		//预编译的SQL执行对象
		PreparedStatement stat = null;
		ResultSet rs = null;
		try {
			conn = DBUtils.getConn();
			String sql = "insert into jdbcuser values(null,?,?)";
			stat = conn.prepareStatement(sql);
			//把？ 替换成真正的数据
			stat.setString(1, name);
			stat.setInt(2, age);
			//执行SQL语句
			stat.executeUpdate();

			System.out.println("插入数据成功！");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtils.closeSource(conn, stat, rs);
		}
	}	
11、批量插入练习
	public static void main(String[] args) {
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		try {
			conn = DBUtils.getConn();
			stat = conn.createStatement();
			String sql1="insert into jdbcuser values(null,'张三',20)";
			String sql2="insert into jdbcuser values(null,'李四',21)";
			String sql3="insert into jdbcuser values(null,'王五',22)";				
			//stat.executeUpdate(sql1);
			//stat.executeUpdate(sql2);
			//stat.executeUpdate(sql3);
			//添加到批量操作中
			stat.addBatch(sql1);
			stat.addBatch(sql2);
			stat.addBatch(sql3);
			//执行批量操作
			stat.executeBatch();				
			System.out.println("插入数据成功！");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtils.closeSource(conn, stat, rs);
		}
	}	
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		try {
			long time0 = System.currentTimeMillis();
			conn = DBUtils.getConn();
			String sql = "insert into jdbcuser values(null,?,?)";
			stat = conn.prepareStatement(sql);
			for(int i=1;i<=100;i++){
				stat.setString(1,"name"+i);		//替换 ?
				stat.setInt(2, 100+i);		
				//添加到批量操作
				stat.addBatch();
				//每20条执行一次
				if(i%20==0){
					//执行批量操作
					stat.executeBatch();
					//清除批量操作
					stat.clearBatch();
				}
			}
			stat.executeBatch();			
			long time1 = System.currentTimeMillis();
			System.out.println("插入数据成功！耗时："+(time1 - time0)+"ms");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtils.closeSource(conn, stat, rs);
		}
	}		
12、分页查询
	public static void main(String[] args) {
		Scanner scan=new Scanner(System.in);
		System.out.println("请输入查询页数：");
		int page= Integer.parseInt(scan.nextLine());
		if(page<=0){	//查询页数不可以为负数
			page=1;
		}
		System.out.println("请输入查询条数：");
		int num = Integer.parseInt(scan.nextLine());
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		try {
			conn = DBUtils.getConn();
			String sql = "select * from jdbcuser limit ?,?";
			stat = conn.prepareStatement(sql);
			stat.setInt(1,(page-1)*num);
			stat.setInt(2, num);
			rs=stat.executeQuery();
			while(rs.next()){
				int id=rs.getInt("id");
				String name = rs.getString("name");
				System.out.println("ID:"+id+"  name:"+name);
			}
			System.out.println("插入数据成功！");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtils.closeSource(conn, stat, rs);
		}
	}	
13、转账练习
	public static void main(String[] args) {
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		try {
			conn = DBUtils.getConn();
			conn.setAutoCommit(false);
			stat =conn.createStatement();
			String sql1 = "update person set money=money-2000 where id=1";
			String sql2 = "update person set money=money+2000 where id=2";			
			stat.executeUpdate(sql1);
			stat.executeUpdate(sql2);
			//得到超人剩余的钱数，如果大于等于0，则允许转账，否则回滚
			rs=stat.executeQuery("select money from person where id=1");
			while(rs.next()){
				int money = rs.getInt("money");
				if(money>=0){
					System.out.println("转账成功！");
					conn.commit();	//提交
				}
				else{
					System.out.println("转账失败！");
					conn.rollback();	//回滚
				}
			}
			System.out.println("操作成功！");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtils.closeSource(conn, stat, rs);
		}
	}	
13、向表中插入数据,队名并且获取主键，根据主键插入队员，实现关系表
	public static void main(String[] args) {
		//createTable();
		Scanner scan=new Scanner(System.in);
		System.out.println("请输入球队名称：");
		String teamName = scan.nextLine();
		System.out.println("请输入球员名称：");
		String playName = scan.nextLine();
		scan.close();
		Connection conn = null; 
		PreparedStatement stat = null;
		ResultSet rs = null;
		try {
			conn = DBUtils.getConn();
			/**
			* 查询是否存在用户输入的球队
			*/
			String sql = "select * from team where name=?";
			stat = conn.prepareStatement(sql);
			stat.setString(1, teamName);
			rs = stat.executeQuery();
			int tid =-1;
			while(rs.next()){
				//得到已经保存的球队id
				tid = rs.getInt("id");
			}
			//判断是否保存过球队
			if(tid==-1){
				sql = "insert into team values(null,?)";
				stat.close();
				//获取主键值
				stat = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);	
				stat.setString(1, teamName);
				stat.executeUpdate();
				rs.close();
				//得到主键值
				rs = stat.getGeneratedKeys();
				while(rs.next()){
					tid = rs.getInt(1);
					System.out.println("teamId="+tid);
				}			
			}
			//插入球员
			sql = "insert into player values(null,?,?)";
			stat.close();
			stat = conn.prepareStatement(sql);
			stat.setString(1, playName);
			stat.setInt(2, tid);
			//执行
			stat.executeUpdate();
			System.out.println("操作成功！");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtils.closeSource(conn, stat, rs);
		}
	}	
14、获取元数据
	public static void main(String[] args) {
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		try {
			conn = DBUtils.getConn();
			//得到数据库的元数据
			DatabaseMetaData dbmd=conn.getMetaData();
			System.out.println("驱动版本："+dbmd.getDriverMajorVersion());
			System.out.println("链接地址："+dbmd.getURL());
			System.out.println("用户名："+dbmd.getUserName());
			System.out.println("数据库厂商："+dbmd.getDatabaseProductName());		
			stat=conn.createStatement();
			rs = stat.executeQuery("select * from emp");
			//从结果集体中得到表的元数据
			ResultSetMetaData rsmd=rs.getMetaData();
			int count = rsmd.getColumnCount();
			//得到表字段的数量
			System.out.println("表字段数量："+count);
			//得到每个字段的字段名和类型
			for(int i=0;i<count;i++){
				String name = rsmd.getColumnName(i+1).toLowerCase();
				String type= rsmd.getColumnTypeName(i+1).toLowerCase();
				System.out.println(name+"  "+type);
			}
				
			System.out.println("操作成功！");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtils.closeSource(conn, stat, rs);
		}
	}