三、JDBC操作

一、步骤

1.获得链接：：1加载驱动→2建立连接

2.操作数据：：3创建语句→4执行语句→5处理数据

3.释放资源：：6关闭连接

二、过程

1.将mysqlJ的DBC支持文件放src的新建文件夹lib中

2.在day15上

3.代码

4.补充包：CTRL+SHIFT+O，选择sql

三、关键语句

获取数据

package lib;

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

public class Demo
{
	public static void main(String[] args) throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection=
				DriverManager.getConnection(
						"jdbc:mysql://localhost:3306/course",
						"root",
						"QE88585");
		//创建语句
		Statement stmt = connection.createStatement();
		//执行语句
		ResultSet rs = stmt.executeQuery("SELECT * FROM student");//返回结果rs
		//遍历结果
		while(rs.next()) //固定写法
		{
			int id = rs.getInt("id");
			String name = rs.getString("name");
			String sex = rs.getString("sex");
			int age = rs.getInt("age");
			double salary = rs.getDouble("salary");
			
			System.out.println("id:"+id+"',name is:"+name+"',sex is:"+sex+"',age is:"+age+"',salary is:"+salary);
		}
		//断开链接，释放资源
		rs.close();			//结果集
		stmt.close();   	//异常
		connection.close();	//链接
	}
}

修改数据

package lib;

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

public class Demo2
{
	public static void main(String[] args) throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection=
				DriverManager.getConnection(
						"jdbc:mysql://localhost:3306/course",
						"root",
						"QE88585");
		//创建语句
		Statement stmt = connection.createStatement();
		//执行语句
//		int n = stmt.executeUpdate("INSERT INTO object(id,name,user_id) VALUES (8,'刀子',5)");//返回结果rs
//		System.out.printf("插入%d条",n);

//		int t = stmt.executeUpdate("UPDATE object SET name ='Knief' WHERE id=8");
//		System.out.printf("修改%d条",t);		
		
		int d = stmt.executeUpdate("DELETE FROM object WHERE id=8");
		System.out.printf("删除%d条",d);	
		
		//断开链接，释放资源
		stmt.close();   	//异常
		connection.close();	//链接
	}
}

预编译型

package lib;

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

public class Demo3
{
	public static void main(String[] args) throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection=
				DriverManager.getConnection(
						"jdbc:mysql://localhost:3306/course",
						"root",
						"QE88585");
		//更新数据
//		//创建语句
//		PreparedStatement statement = connection.prepareStatement(
//				"UPDATE student SET name=? WHERE name is NULL");
//		//设定参数
//		statement.setString(1,"kim");	//执行语句
//		statement.executeUpdate();

		//插入数据
		//创建语句
		PreparedStatement statement = connection.prepareStatement(
				"INSERT INTO object(id,name,user_id) VALUES (?,?,?)");
		//设定参数
		statement.setInt(1,11);
		statement.setString(2,"kim");	
		statement.setInt(3,5);	
		
		statement.executeUpdate();
		
		
		//查阅数据
//		//创建语句
//		PreparedStatement statement = connection.prepareStatement(
//				"SELECT * FROM student WHERE id=?");
//		//设定参数
//		statement.setInt(1,1);	//执行语句
//		ResultSet rs = statement.executeQuery();
//		//遍历结果
//		while(rs.next()) //固定写法
//		{
//			int id = rs.getInt("id");
//			String name = rs.getString("name");
//			String sex = rs.getString("sex");
//			int age = rs.getInt("age");
//			double salary = rs.getDouble("salary");
//			
//			System.out.println("id:"+id+"',name is:"+name+"',sex is:"+sex+"',age is:"+age+"',salary is:"+salary);
//		}
		
		//断开链接，释放资源
		//rs.close();			//结果集
		statement.close();  //异常
		connection.close();	//链接
	}
}

异常与错误

package lib;

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

public class Demo4
{
	public static void main(String[] args)
	{
		Connection connection		=null;
		PreparedStatement statement =null;
		ResultSet rs 				=null;
		
		try
		{
			Class.forName("com.mysql.jdbc.Driver");
			connection=
					DriverManager.getConnection(
							"jdbc:mysql://localhost:3306/course",
							"root",
							"QE88585");		
			//查阅数据
			//创建语句
			statement = connection.prepareStatement(
					"SELECT * FROM student WHERE id=?");
			//设定参数
			statement.setInt(1,1);	//执行语句
			rs = statement.executeQuery();
			//遍历结果
			while(rs.next()) //固定写法
			{
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String sex = rs.getString("sex");
				int age = rs.getInt("age");
				double salary = rs.getDouble("salary");
				
				System.out.println("id:"+id+"',name is:"+name+"',sex is:"+sex+"',age is:"+age+"',salary is:"+salary);
			}
		}
		catch (Exception e){
			e.printStackTrace();
		}
		finally //finally是无论程序运行成什么状况，都会运行的部分，以保证 释放资源
		{
			if(rs!=null) {//由于可能会在rs语句之前出错，需要判断rs是否执行了再来关闭，如果没有执行就关闭，又是异常从而影响后续程序的运行
				try {//由于.close()也有异常的可能，所以需要Try的形式来容纳可能会出现的异常
					rs.close();			//结果集
				}
				catch (Exception e2){
					e2.printStackTrace();
				}
			}
			
			if(statement!=null) {
				try {
					statement.close();  //异常
				} 
				catch (Exception e2){
					e2.printStackTrace();
				}
			}
			
			if(connection!=null) {
				try{
					connection.close();	//链接
				} 
				catch (Exception e2){
					e2.printStackTrace();
				}
			}
		}
	}
}