1.PreparedStatement
package servletDemo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCPreparedStatement {
private static final String URL = "jdbc:MySQL://localhost:3306/hotel?&useSSL=false&serverTimezone=UTC";
private static final String USERNAME = "root";
private static final String PWD = "root";
public static void update(){//增删改
Connection connection = null;//因为有可能还么有用到connection就报错了 那么这个对象是空的就不能使用close方法
PreparedStatement pstmt = null;//同理
try{
//1导入驱动,加载具体的驱动类
Class.forName("com.mysql.cj.jdbc.Driver");
//2与DB建立连接
connection = DriverManager.getConnection(URL, USERNAME, PWD);
//3发送sql语句,执行(增删改)
//pstmt需要预编译sql
int stunumber = 3;
String sname = "WangWu";
String sql = "insert into student values (?,?)";
pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, stunumber);
pstmt.setString(2,sname);
// String sql = "insert into student values (1,'zs',23,'s1')";
//String sql = "update student set name = 'tian' where number = 1";
//String sql = "delete from student where number = 1";
//执行SQL
int count = pstmt.executeUpdate();//返回表示增删改 了几条数据
//4处理结果集
if(count>0){
System.out.println("操作成功!");
}
}catch (ClassNotFoundException e) {//Class.forName()的异常
e.printStackTrace();
} catch (SQLException e) {//connection.createStatement()异常
e.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
}finally{//用finally的原因是无论有没有异常都需要关闭连接
try{
if(pstmt!=null){//执行sql语句
pstmt.close();
}
if(connection!=null){//建立java代码和数据库的连接
connection.close();
}
}catch(SQLException e) {//前面两个close 都会抛SQLException异常
e.printStackTrace();
}
}
}
public static void query(){//查
Connection connection = null;//因为有可能还么有用到connection就报错了 那么这个对象是空的就不能使用close方法
PreparedStatement pstmt = null;//同理
ResultSet rs = null;
try{
//1导入驱动,加载具体的驱动类
Class.forName("com.mysql.cj.jdbc.Driver");
//2与DB建立连接
connection = DriverManager.getConnection(URL, USERNAME, PWD);
//3发送sql语句,执行(增删改)
String name = "%W%";
String sql = "select * from student where stuname like ?";
pstmt = connection.prepareStatement(sql);
pstmt.setString(1, name);
// pstmt.setString(2, "stuname");
// pstmt.setString(3, "China");
//
//执行SQL
rs = pstmt.executeQuery();//返回表示增删改 了几条数据
//4处理结果集
//rs指向结果集的前一个元素 结果集相当于一张表
while(rs.next()){//判断rs指向的下一个元素有没有数据
int sno = rs.getInt("stuno");
//int sno = rs.getInt(1);
String sname = rs.getString("stuname");//里面写列名
//也可以写序号 一行中的第几列 但是不推荐
//String sname = rs.getString(2);
System.out.println(sno + "--"+sname);
}
}catch (ClassNotFoundException e) {//Class.forName()的异常
e.printStackTrace();
} catch (SQLException e) {//connection.createStatement()异常
e.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
}finally{//用finally的原因是无论有没有异常都需要关闭连接
try{
if(pstmt!=null){//执行sql语句
pstmt.close();
}
if(connection!=null){//建立java代码和数据库的连接
connection.close();
}
}catch(SQLException e) {//前面两个close 都会抛SQLException异常
e.printStackTrace();
}
}
}
public static void main(String [] args){
update();
// query();
}
}
2.Statement
package servletDemo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCStatement {
private static final String URL = "jdbc:MySQL://localhost:3306/hotel?&useSSL=false&serverTimezone=UTC";
private static final String USERNAME = "root";
private static final String PWD = "root";
public static void update(){//增删改
Connection connection = null;//因为有可能还么有用到connection就报错了 那么这个对象是空的就不能使用close方法
Statement stmt = null;//同理
try{
//1导入驱动,加载具体的驱动类
Class.forName("com.mysql.cj.jdbc.Driver");
//2与DB建立连接
connection = DriverManager.getConnection(URL, USERNAME, PWD);
//3发送sql语句,执行(增删改)
stmt = connection.createStatement();
String sql = "insert into student values (6,'Wanmei')";
// String sql = "update student set stuname = 'tian' where stuno = 1";
// String sql = "delete from student where stuno = 7";
//执行SQL
int count = stmt.executeUpdate(sql);//返回表示增删改 了几条数据
//4处理结果集
if(count>0){
System.out.println("操作成功!");
}
}catch (ClassNotFoundException e) {//Class.forName()的异常
e.printStackTrace();
} catch (SQLException e) {//connection.createStatement()异常
e.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
}finally{//用finally的原因是无论有没有异常都需要关闭连接
try{
if(stmt!=null){//执行sql语句
stmt.close();
}
if(connection!=null){//建立java代码和数据库的连接
connection.close();
}
}catch(SQLException e) {//前面两个close 都会抛SQLException异常
e.printStackTrace();
}
}
}
public static void query(){//查
Connection connection = null;//因为有可能还么有用到connection就报错了 那么这个对象是空的就不能使用close方法
Statement stmt = null;//同理
ResultSet rs = null;
try{
//1导入驱动,加载具体的驱动类
Class.forName("com.mysql.cj.jdbc.Driver");
//2与DB建立连接
connection = DriverManager.getConnection(URL, USERNAME, PWD);
//3发送sql语句,执行(增删改)
stmt = connection.createStatement();
String sql = "select stuno,stuname from student";
//执行SQL
rs = stmt.executeQuery(sql);//返回表示增删改 了几条数据
//4处理结果集
//rs指向结果集的前一个元素 结果集相当于一张表
while(rs.next()){//判断rs指向的下一个元素有没有数据
int sno = rs.getInt("stuno");
//int sno = rs.getInt(1);
String sname = rs.getString("stuname");//里面写列名
//也可以写序号 一行中的第几列 但是不推荐
//String sname = rs.getString(2);
System.out.println(sno + "--"+sname);
}
}catch (ClassNotFoundException e) {//Class.forName()的异常
e.printStackTrace();
} catch (SQLException e) {//connection.createStatement()异常
e.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
}finally{//用finally的原因是无论有没有异常都需要关闭连接
try{
if(stmt!=null){//执行sql语句
stmt.close();
}
if(connection!=null){//建立java代码和数据库的连接
connection.close();
}
}catch(SQLException e) {//前面两个close 都会抛SQLException异常
e.printStackTrace();
}
}
}
public static void main(String [] args){
update();
query();
}
}