一、步骤
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();
}
}
}
}
}