案例: JDBCDemo2.jsp
package com.jdbc.com;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCDemo2 {
private static final String url = "jdbc:mysql://localhost:3306/jsp02";
private static final String user = "root";
private static final String password = "123456";
public static void query() {// 增删改
Connection connection = null;
Statement stmt = null;
ResultSet rs = null;
try {
// a.导入驱动,加载具体的驱动类
Class.forName("com.mysql.jdbc.Driver");// 加载具体的驱动类
// b.与数据库建立连接
connection = DriverManager.getConnection(url, user, password);
// c.发送sql,执行(增删改、查)
stmt = connection.createStatement();
// 执行sql(增删改executeUpdate(),查询为executeQuery()
String sql = "select stuno,stuname from student";
rs = stmt.executeQuery(sql);// 返回值表示增删改了几条数据
// d.处理结果,增删改判断结果就行了
while(rs.next()) {
int sno = rs.getInt("stuno");
String sname = rs.getString("stuname");
System.out.println(sno+"----"+sname);
}
}catch(ClassNotFoundException e) {
e.printStackTrace();
}catch(SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(rs!=null)rs.close();
if(stmt!=null) stmt.close();
if(connection!=null) connection.close();
connection.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
//update();
query();
}
}
表中原有数据:
执行结果:
//也可以这样写,跟表中的字段一一对应,只适用于上面的字段查询,
如果查询的是 select * from student,就不适用了。下标从1开始
int sno = rs.getInt(1);
String sname = rs.getString(2);
System.out.println(sno+"----"+sname);
String sql = "select * from student where stuname like '%z%' ";
结果:
字符的拼接: 把上面模糊查询的语句改为如下:用变量的形式表示
String name = "z";
String sql = "select * from student where stuname like '%"+name+"%' ";
结果还是一样的:
Connection产生Statement对象:createStatement() Connection产生PreparedStatement对象:prepareStatement() Connection产生CallableStatement对象:prepareCall();
select count(*) from login where uname=’"+name+"’ and upwd =’"+pwd+"’
案例1:使用preparedStatement 增删改 数据 JDBCPreparedStatementDemo.jsp
package com.jdbc.com;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.PreparedStatement;
public class JDBCPreparedStatementDemo {
private static final String url = "jdbc:mysql://localhost:3306/jsp03";
private static final String user = "root";
private static final String password = "123456";
public static void update() {// 增删改
Connection connection = null;
PreparedStatement pstmt = null;
try {
// a.导入驱动,加载具体的驱动类
Class.forName("com.mysql.jdbc.Driver");// 加载具体的驱动类
// b.与数据库建立连接
connection = DriverManager.getConnection(url, user, password);
// c.发送sql,执行(增删改、查)
/*Statement
stmt = connection.createStatement();
// 执行sql
//String sql = "insert into student values(1,'zs',23,'s1')";
//String sql = "update student set stuname='ls' where stuno=1";
String sql = "delete from student where stuno=1";
int count = stmt.executeUpdate(sql);// 返回值表示增删改了几条数据
*/
//PreparedStatement
String sql = "insert into student values(4,'xi',30,'s4')";
pstmt = connection.prepareStatement(sql);//把sql提前处理
int count = pstmt.executeUpdate();
// d.处理结果,增删改判断结果就行了
if (count > 0) {
System.out.println("操作成功!");
}
}catch(ClassNotFoundException e) {
e.printStackTrace();
}catch(SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(pstmt!=null) pstmt.close();
if(connection!=null) connection.close();
connection.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
update();
}
}
表中原数据:
执行结果:
将上面的SQL语句改为: 增、删、改都是同样的格式
//先用“?”充当占位符,然后通过setXxx来设置值
String sql = "insert into student values(?,?,?,?)";
pstmt = connection.prepareStatement(sql);//把sql提前处理
pstmt.setInt(1, 05);//前面的参数表示和上面“?”对应的下标,第二个参数是对应的值。
pstmt.setString(2, "小明");
pstmt.setInt(3, 25);
pstmt.setString(4, "s5");
执行结果:
案例2:使用preparedStatement 查询数据 JDBCpreparedStatementDemo2.jsp
package com.jdbc.com;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
public class JDBCpreparedStatementDemo2 {
private static final String url = "jdbc:mysql://localhost:3306/jsp02";
private static final String user = "root";
private static final String password = "123456";
public static void query() {// 增删改
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
// a.导入驱动,加载具体的驱动类
Class.forName("com.mysql.jdbc.Driver");// 加载具体的驱动类
// b.与数据库建立连接
connection = DriverManager.getConnection(url, user, password);
// c.发送sql,执行(增删改、查)
String sql = "select * from student where stuname like ?";
pstmt = connection.prepareStatement(sql);
pstmt.setString(1, "%z%");
// 执行sql(增删改executeUpdate(),查询为executeQuery()
//String sql = "select stuno,stuname from student";
//模糊查询(一)
//String sql = "select * from student where stuname like '%z%' ";
//模糊查询(二)
//String name = "z";
//String sql = "select * from student where stuname like '%"+name+"%' ";
rs = pstmt.executeQuery();// 返回值表示增删改了几条数据
// d.处理结果,增删改判断结果就行了
while(rs.next()) {
//也可以这样写,跟表中的字段一一对应,只适用于上面的字段查询,如果查询的是 select * from student,就不适用了,下标从1开始
//int sno = rs.getInt(1);
//String sname = rs.getString(2);
int sno = rs.getInt("stuno");
String sname = rs.getString("stuname");
System.out.println(sno+"----"+sname);
}
}catch(ClassNotFoundException e) {
e.printStackTrace();
}catch(SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(rs!=null)rs.close();
if(pstmt!=null) pstmt.close();
if(connection!=null) connection.close();
connection.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
//update();
query();
}
}
原表数据:
结果:
一般建议使用PreparedStatement来增删改查数据