1、Statement接口实现增删改查
（1）增加数据表中的元组：
package pers.datebase.zsgc; import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement; public class JDBC {
public static void main(String[] args) throws ClassNotFoundException,
SQLException {
// 加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 链接到数据库
String url = "jdbc:mysql://localhost:3306/student mangement system";
String username = "root";
String password = "root";
Connection con = DriverManager.getConnection(url, username, password); // 获取到SQL语句的执行者对象
Statement stat = con.createStatement(); // 增加
stat.executeUpdate("insert into student(studentno,sname,sex,birthday,classno,point,phone,email)"
+ "values('201712219','张涵','女','1989-12-18','080601','666','15878945612','[email protected]')"); // 释放资源
stat.close();
con.close();
}
}
（2）删除元组：
package pers.datebase.zsgc; import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement; public class JDBC {
public static void main(String[] args) throws ClassNotFoundException,
SQLException {
// 加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 链接到数据库
String url = "jdbc:mysql://localhost:3306/student mangement system";
String username = "root";
String password = "root";
Connection con = DriverManager.getConnection(url, username, password); // 获取到SQL语句的执行者对象
Statement stat = con.createStatement(); // 删除
String sql = "delete from student where sname='张涵'"; // SQL语句
stat.executeUpdate(sql); // 将sql语句上传至数据库执行 // 释放资源
stat.close();
con.close();
}
}
（3）修改元组：
package pers.datebase.zsgc; import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement; public class JDBC {
public static void main(String[] args) throws ClassNotFoundException,
SQLException {
// 加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 链接到数据库
String url = "jdbc:mysql://localhost:3306/student mangement system";
String username = "root";
String password = "root";
Connection con = DriverManager.getConnection(url, username, password); // 获取到SQL语句的执行者对象
Statement stat = con.createStatement(); String sql="update student set classno='201718' where sname='平靖' "; //SQL语句
stat.executeUpdate(sql); //将sql语句上传至数据库执行 // 释放资源
stat.close();
con.close();
}
}
（4）查询数据表：
package pers.datebase.zsgc; import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement; public class JDBC {
public static void main(String[] args) throws ClassNotFoundException,
SQLException {
// 加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 链接到数据库
String url = "jdbc:mysql://localhost:3306/student mangement system";
String username = "root";
String password = "root";
Connection con = DriverManager.getConnection(url, username, password); // 获取到SQL语句的执行者对象
Statement stat = con.createStatement(); String sql="select * from score"; //sql语句
ResultSet rs=stat.executeQuery(sql); //ResultSet 是java中执行select后，返回的结果集类。 rs 就是结果集的变量。
while(rs.next()){ //next（）获取里面的内容
System.out.println(rs.getString()+" "+rs.getString()+" "+rs.getString()+""+rs.getString());
} // 释放资源
stat.close();
con.close();
rs.close();
}
}
2、PreparedStatement接口
（1）添加：
package pers.Pre.add; import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement; public class Add {
public static void main(String[] args) {
Connection con = null;
PreparedStatement ps = null; try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/student mangement system";
String username = "root";
String password = "root";
con = DriverManager.getConnection(url, username, password);
String insertSql = "insert into score(studentno,courseno,usually,final)values(?,?,?,?)";
ps = con.prepareStatement(insertSql);// 获取预处理对象
ps.setString(1, "20191832");
ps.setString(2, "b221");
ps.setDouble(3, 123);
ps.setDouble(4, 123);
int num = ps.executeUpdate();
System.out.println("添加了" + num + "条记录");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (ps != null)
ps.close();
if (con != null)
con.close();
} catch (Exception e) {
e.printStackTrace();
}
} }
}
（2）删除：
package pers.Pre.jdbc; import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement; public class DeleteDemo {
public static void main(String[] args) {
Connection con = null;
PreparedStatement ps = null; try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/student mangement system";
String username = "root";
String password = "root";
con = DriverManager.getConnection(url, username, password);
String deleteSql = "Delete from Student where sname=?";
ps = con.prepareStatement(deleteSql);// 获取预处理对象
ps.setString(1,"秀儿");
int num = ps.executeUpdate();
System.out.println("删除了" + num + "条信息");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (ps != null)
ps.close();
if (con != null)
con.close();
} catch (Exception e) {
e.printStackTrace();
} } } }
（3）修改：
package pers.Pre.jdbc; import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement; public class UpdateDemo {
public static void main(String[] args) {
Connection con = null;
PreparedStatement ps = null; try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/student mangement system";
String username = "root";
String password = "root";
con = DriverManager.getConnection(url, username, password);
String updateSql = "Update Login set password=? where account ='学前班秀儿'";
ps = con.prepareStatement(updateSql);// 获取预处理对象
ps.setString(1,"201908");
int num = ps.executeUpdate();
System.out.println("更改了" + num + "条信息");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (ps != null)
ps.close();
if (con != null)
con.close();
} catch (Exception e) {
e.printStackTrace();
} } } }
（4）查询：
package pers.Pre.jdbc; import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet; public class SelectDemo {
public static void main(String[] args) {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null; try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/student mangement system";
String username = "root";
String password = "root";
con = DriverManager.getConnection(url, username, password);
String selectSql = "select * from score";
ps = con.prepareStatement(selectSql);// 获取预处理对象
rs = ps.executeQuery();
System.out.println(" "+"学号"+" "+"班级"+" "+"平时成绩"+" "+"期末成绩");
while (rs.next()) { System.out.println( rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+
rs.getString(4));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (ps != null)
ps.close();
if (con != null)
con.close();
if (rs!=null)
rs.close();
} catch (Exception e) {
e.printStackTrace();
} } } }
（5）Statement接口与PreparedStatement接口的对比：
Statement接口用于处理不带参数的静态sql语句，PreparedStatement接口可以处理带参数的sql语句。
（6）防注入攻击
可以注入攻击的登录：
package pers.jdbc.log; import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*; public class Log { public static void main(String[] args) throws Exception {
// 输入用户名和密码
Scanner sc = new Scanner(System.in);
System.out.println("请输入账号");
String zh = sc.nextLine();
System.out.println("请输入密码");
String mm = sc.nextLine(); // 到数据库验证用户名和密码是否正确
Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://localhost:3306/student mangement system";
String username = "root";
String password = "root";
Connection con = DriverManager.getConnection(url, username, password); Statement state = con.createStatement(); String sql = "select * from Login where account='" + zh
+ "' and password='" + mm + "'";
ResultSet re = state.executeQuery(sql);
// 输出：正确显示欢迎，不正确显示错误
if (re.next()) {
System.out.println("登陆成功！");
} else {
System.out.println("输入账号或密码错误");
}
state.close();
con.close();
re.close(); } }
可以看出，在登陆的数据表中，并没有与之相对应的用户名和密码，但是也能登录成功。 其实在java代码中，运行的查询语句是这样的：select * from Login where account='zhai'and password='1' or 1=1，运行结果为true。
注意：对是否登录成功的判断不能以是否为空作为标准，要以next()方法作为标准去判断
注入攻击的防止：
package pers.jdbc.log; import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner; public class Log1 {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/student mangement system";
String username = "root";
String password = "root";
Connection con = DriverManager.getConnection(url, username, password);
Scanner sc = new Scanner(System.in);
System.out.println("请输入账号");
String account = sc.nextLine();
System.out.println("请输入密码");
String password1 = sc.nextLine();
// 执行SQL语句,数据表,查询用户名和密码,如果存在,登录成功,不存在登录失败
String sql = "SELECT * FROM login WHERE account=? AND password=?";
// 调用Connection接口的方法prepareStatement,获取PrepareStatement接口的实现类
// 方法中参数,SQL语句中的参数全部采用问号占位符
PreparedStatement pst = con.prepareStatement(sql); // 调用p s t对象set方法,设置问号占位符上的参数
pst.setObject(1, account);
pst.setObject(2, password1); // 调用方法,执行SQL,获取结果集
ResultSet rs = pst.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("account") + "欢迎你");
}
rs.close();
pst.close();
con.close();
sc.close();
}
}
在java代码中，如果还进行注入攻击，运行的查询语句是这样的：select * from Login where account='zhai'and password=1' or ‘1=1，将运行错误。
3、DBUtils实现增删改查（QueryRunner类）
利用QueryRunner类实现对数据库的增删改查操作，需要先导入jar包：commons-dbutils-1.6。利用QueryRunner类可以实现对数据步骤的简化。
（1）添加：
运用JDBC工具类实现连接：
package JDBCUtils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties; public class JDBCUtils {
private static Connection con;
private static String driver;
private static String url;
private static String username;
private static String password; static {// 静态代码块只执行一次，获取一次信息即可
try {
readConfig();
Class.forName(driver);
con = DriverManager.getConnection(url, username, password);
} catch (Exception ex) {
throw new RuntimeException("数据库连接失败");
}
}
/*
* getClassLoader();返回该类的加载器
* getResourceAsStream();查找具有给定名称的资源
*/
private static void readConfig() {
InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("JDBC.properties");
Properties pro = new Properties();
try {
pro.load(in);
} catch (IOException e) {
e.printStackTrace();
}
driver = pro.getProperty("driver");
url = pro.getProperty("url");
username = pro.getProperty("username");
password = pro.getProperty("password");
} public static Connection getConnection() {
return con;
}
public static void close(Connection con) { if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
System.out.println("con流关闭异常！");
}
} }
public static void close(Connection con, Statement stat) { if (stat != null) {
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
System.out.println("stat流关闭异常！");
}
} if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
System.out.println("con流关闭异常！");
}
} } public static void close(Connection con, Statement stat, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
System.out.println("rs流关闭异常！");
}
} if (stat != null) {
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
System.out.println("stat流关闭异常！");
}
} if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
System.out.println("con流关闭异常！");
}
} }
}
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner; import JDBCUtils.JDBCUtils; public class add { public static void main(String[] args) {
Connection con = null;
try {
con = JDBCUtils.getConnection();
QueryRunner qr = new QueryRunner();
String sql = "INSERT INTO student(studentno,sname,sex,birthday,classno,point,phone,email) VALUES(?,?,?,?,?,?,?,?)";
Object[] params = { "20191811", "Jack", "男", "1988-12-01",
"201901", "239", "16623540978", "[email protected]" }; int num = qr.update(con, sql, params);
System.out.println("添加了" + num + "行"); } catch (SQLException e) {
throw new RuntimeException(e);
}
JDBCUtils.close(con);
}
}
（2）删除：
import java.sql.Connection;
import java.sql.SQLException; import org.apache.commons.dbutils.QueryRunner; import JDBCUtils.JDBCUtils; public class DeleteDemo {
public static void main(String[] args) {
Connection con = null;
try {
con = JDBCUtils.getConnection();
QueryRunner qr = new QueryRunner();
String sql = "DELETE from Student where sname =?";
Object[] delete = { "Tom" };
qr.update(con, sql, delete); } catch (SQLException e) {
throw new RuntimeException(e);
}
JDBCUtils.close(con);
}
}
（3）修改：
import java.sql.Connection;
import java.sql.SQLException; import org.apache.commons.dbutils.QueryRunner; import JDBCUtils.JDBCUtils; public class UpdateDemo {
public static void main(String[] args) {
Connection con = null;
try {
con = JDBCUtils.getConnection();
QueryRunner qr = new QueryRunner();
String sql = "Update Student set classno=? Where sname='韩吟秋'";
Object[] update = { "201901" };
qr.update(con, sql, update); } catch (SQLException e) {
throw new RuntimeException(e);
}
JDBCUtils.close(con);
}
}
（4）查询：
ArrayHandler： 将结果集的第一行存储到Object[]数组中
ArrayListHandler： 将结果集的每一行存储到Object[]数组中
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List; import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler; import JDBCUtils.JDBCUtils; public class SeleteDemo {
public static void main(String[] args) {
Connection con = null;
try {
con = JDBCUtils.getConnection();
QueryRunner qr = new QueryRunner();
String sql = "Select * from Student where studentno=?";
Object[] select = { 20191811 };
List<Object[]> list = qr.query(con, sql, new ArrayListHandler(),
select);
// 将记录封装到一个装有Object[]的List集合中
for (Object[] arr : list) {
System.out.println(Arrays.toString(arr));
} } catch (SQLException e) {
throw new RuntimeException(e);
}
JDBCUtils.close(con);
} }
BeanHandler：结果集中第一条记录封装到一个指定的javaBean中。
BeanListHandler：结果集中每一条记录封装到javaBean中，再将javaBean封装到list集合中。
public class Student {
private String studentno;
private String sname;
private String sex;
private String birthday;
private String classno;
private String point;
private String phone;
private String email;
public String getStudentno() {
return studentno;
}
public void setStudentno(String studentno) {
this.studentno = studentno;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getBirthday() {
return birthday;
}
public void setBirthday(String birthday) {
this.birthday = birthday;
}
@Override
public String toString() {
return "Student [studentno=" + studentno + ", sname=" + sname + ", sex="
+ sex + ", birthday=" + birthday + ", classno=" + classno
+ ", point=" + point + ", phone=" + phone + ", email=" + email
+ "]";
}
public String getClassno() {
return classno;
}
public void setClassno(String classno) {
this.classno = classno;
}
public String getPoint() {
return point;
}
public void setPoint(String point) {
this.point = point;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
}import java.sql.Connection;
import java.sql.SQLException;
import java.util.List; import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler; import JDBCUtils.JDBCUtils; public class SeleteDemo {
public static void main(String[] args) {
Connection con = null;
try {
con = JDBCUtils.getConnection();
QueryRunner qr = new QueryRunner();
String sql = "Select * from Student where studentno=?";
Object[] select = { 20191811 };
List<Student> list = qr.query(con, sql,new BeanListHandler<Student>((Student.class)), select);
// 将记录封装到一个装有Object[]的List集合中
for (Student s : list) {
System.out.println(s);
} } catch (SQLException e) {
throw new RuntimeException(e);
}
JDBCUtils.close(con);
} }
ColumnListHandler将结果集中指定的列封装到List集合。
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List; import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ColumnListHandler; import JDBCUtils.JDBCUtils; public class SeleteDemo {
public static void main(String[] args) {
Connection con = null;
try {
con = JDBCUtils.getConnection();
QueryRunner qr = new QueryRunner();
String sql = "Select * from Student where studentno=?";
Object[] select = {20191811};
List<String> list = qr.query(con, sql,new ColumnListHandler<String>(), select);
// 将记录封装到一个装有Object[]的List集合中
for (String str: list) {
System.out.println(str);
} } catch (SQLException e) {
throw new RuntimeException(e);
}
JDBCUtils.close(con);
} }
查询学生的学号：
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List; import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ColumnListHandler; import JDBCUtils.JDBCUtils; public class SeleteDemo {
public static void main(String[] args) {
Connection con = null;
try {
con = JDBCUtils.getConnection();
QueryRunner qr = new QueryRunner();
String sql = "Select studentno from Student ";
Object[] select = {};
List<String> list = qr.query(con, sql,new ColumnListHandler<String>(), select);
// 将记录封装到一个装有Object[]的List集合中
for (String str: list) {
System.out.println(str);
} } catch (SQLException e) {
throw new RuntimeException(e);
}
JDBCUtils.close(con);
} }
ScalarHandler返回一个数据
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ScalarHandler; import JDBCUtils.JDBCUtils; public class SeleteDemo {
public static void main(String[] args) {
Connection con = null;
try {
con = JDBCUtils.getConnection();
QueryRunner qr = new QueryRunner();
String sql = "SELECT COUNT(sname) FROM Student";
Object[] select = {};
long count= qr.query(con, sql, new ScalarHandler<Long>(), select);
System.out.println(count);
} catch (SQLException e) {
throw new RuntimeException(e);
}
JDBCUtils.close(con);
} }
MapHandler：将结果集的第一行封装到Map集合中
MapListHandler：将结果集的多条记录封装到一个集合中
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map; import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler; import JDBCUtils.JDBCUtils; public class SeleteDemo {
public static void main(String[] args) {
Connection con = null;
try {
con = JDBCUtils.getConnection();
QueryRunner qr = new QueryRunner();
String sql = "Select studentno from Student ";
Object[] select = {};
List<Map<String,Object>> list = qr.query(con, sql, new MapListHandler(),select); // 将记录封装到一个装有Object[]的List集合中
for (Map<String,Object> map : list) {
for(String key : map.keySet()){
System.out.print(key+"..."+map.get(key));
}
System.out.println();
} } catch (SQLException e) {
throw new RuntimeException(e);
}
JDBCUtils.close(con);
} }