一、什么是JDBC
JDBC:JAVA DATEBASE CONNECTION
JDBC是一种用于执行SQL语句的Java API,可以为多种关系数据库提供统一访问,它由一组用Java语言编写的类和接口组成。简单地说,JDBC 可做三件事:与数据库建立连接、发送 SQL 语句并处理结果。
二、JDBC七步骤
1.加载驱动
2.创建连接
3.写sql命令
4.得到statement对象
5.执行sql,得到结果集
6.处理结果集
7.关闭资源
三、idea源代码
1、插入(增)
package com.JDBC;
import util.DBUtil;
import java.sql.*;
import java.util.Scanner;
public class insert {
public static void main(String[] args){
PreparedStatement statement=null;
Connection connection=null;
Scanner scan=new Scanner(System.in);
Scanner scanner=new Scanner(System.in);
try {
//1、2、调用getConn(),加载驱动,创建连接
connection= DBUtil.getConn();
//3.写sql
String sql="insert into tb_user(id,username,password)values (?,?,?)";
//4.得到statement对象
statement=connection.prepareStatement(sql);
//5.执行sql,得到结果集
System.out.print("请输入id:");
int id=scan.nextInt();
System.out.print("请输入用户名:");
String username=scanner.nextLine();
System.out.print("请输入密码:");
String password=scanner.nextLine();
statement.setInt(1, id);
statement.setString(2,username);
statement.setString(3,password);
statement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
//6.关闭资源
DBUtil.close(statement,connection);
}
}
}
2、删除(删)
package com.JDBC;
import util.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Scanner;
public class delete {
public static void main(String[] args){
PreparedStatement statement=null;
Connection connection=null;
Scanner scanner=new Scanner(System.in);
try {
//1、2、调用getConn(),加载驱动,创建连接
connection= DBUtil.getConn();
//3.写sql
String sql="delete from tb_user where (id)=(?)";
//4.得到statement对象
statement=connection.prepareStatement(sql);
//5.执行sql,得到结果集
System.out.print("请输入要删除的id:");
int num=scanner.nextInt();
statement.setInt(1,num);
statement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
//6.关闭资源
DBUtil.close(statement,connection);
}
}
}
3、更新(改)
package com.JDBC;
import util.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Scanner;
public class update {
public static void main(String[] args){
PreparedStatement statement=null;
Connection connection=null;
Scanner scan=new Scanner(System.in);
Scanner scanner=new Scanner(System.in);
try {
//1、2、调用getConn(),加载驱动,创建连接
connection= DBUtil.getConn();
//3.写sql
String sql="update tb_user set username=?,password=? where id=?";
//4.得到statement对象
statement=connection.prepareStatement(sql);
//5、执行sql,得到结果集
System.out.print("请输入要更新的id:");
int num=scan.nextInt();
System.out.print("请输入username:");
String name=scanner.nextLine();
System.out.print("请输入password:");
String password=scanner.nextLine();
statement.setInt(3,num);
statement.setString(1,name);
statement.setString(2,password);
statement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
//6.关闭资源
DBUtil.close(statement,connection);
}
}
}
4、查询(查)
package com.JDBC;
import util.DBUtil;
import java.sql.*;
public class select {
//JDBC JAVA DATEBASE CONNECTION
//1.加载驱动
//2.创建连接
//3.写sql
//4.得到statement对象
//5.执行sql,得到结果集
//6.处理结果集
//7.关闭资源
public static void main(String[] args){
ResultSet resultSet=null;
PreparedStatement statement=null;
Connection connection=null;
try {
//1、2、调用getConn(),加载驱动,创建连接
connection=DBUtil.getConn();
//3.写sql
String sql="select * from tb_user";
//4.得到statement对象
statement=connection.prepareStatement(sql);
//5.执行sql,得到结果集
resultSet=statement.executeQuery();
//6.处理结果集
while (resultSet.next()){
System.out.print(resultSet.getInt(1)+" ");
System.out.print(resultSet.getString(2)+" ");
System.out.println(resultSet.getString(3));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
//7.关闭资源
DBUtil.close2(resultSet,statement,connection);
}
}
}
5、DBUtil类
package util;
import java.sql.*;
public class DBUtil {
public static Connection getConn(){
Connection conn=null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/idea?useSSL=true&" + "characterEncoding=utf-8&user=root&password=123456");
System.out.println("创建连接成功");
return conn;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
public static void close2(ResultSet resultSet, PreparedStatement preparedStatement,Connection connection){
if(resultSet!=null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(preparedStatement!=null) {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection!=null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(PreparedStatement preparedStatement,Connection connection){
if(preparedStatement!=null) {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection!=null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}