防止注入攻击，使用第二种方法
package service;
import java.sql.*;
import java.util.Scanner;
public class LoginDemo {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/company?characterEncoding=utf8&useSSL=true";
String userName = "root";
String passWord = "root";
Connection connection = DriverManager.getConnection(url, userName, passWord);
Statement statement = connection.createStatement();
Scanner sc = new Scanner(System.in);
String use = sc.nextLine();
String pass = sc.nextLine();
// 执行sql语句判断正确还是失败
String sql = "SELECT * FROM login where account = '" + use + "' and password = '" + pass + "'";
/**
* 控制台输入 sql注入
* a
* q 'or' 1=1
*/
System.out.println(sql);
ResultSet res = statement.executeQuery(sql);
while (res.next()) {
System.out.println(res.getObject("account") + res.getString("password"));
}
res.close();
statement.close();
connection.close();
/**
* 防止注入攻击
* 有一个子接口preparedStatement(String sql)
* sql 语句全部用占位符 ？
*/
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/company?characterEncoding=utf8&useSSL=true";
String userName = "root";
String passWord = "root";
Connection connection = DriverManager.getConnection(url, userName, passWord);
Scanner sc = new Scanner(System.in);
String use = sc.nextLine();
String pass = sc.nextLine();
String sql1 = "SELECT * FROM login where account = ? and password = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql1);
preparedStatement.setObject(1,use);
preparedStatement.setObject(2,pass);
ResultSet res = preparedStatement.executeQuery();
while (res.next()) {
System.out.println(res.getObject("account") + res.getString("password"));
}
res.close();
preparedStatement.close();
connection.close();
}
}
引用org.apache.commons.dbutils.DbUtils 封装的这个包
package controller.commonsDButils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
/**
* insert update delete
* QueryRunnner类的update（connect con, String sql , Object ... params）
*/
public class QueryRunnerDemo {
/**
* 获取连接connection con
*/
private static Connection conn;
public static Connection getConnection() {
String driverClassName = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/company?characterEncoding=utf8&useSSL=true";
String username = "root";
String password = "root";
Connection conn = null;
DbUtils.loadDriver(driverClassName);
try {
conn = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void main(String[] args) throws SQLException {
// insert();
// query();
// modify();
delete();
}
public static void insert() throws SQLException {
conn = getConnection();
/**
* 创建QueryaRunner类对象
* 插入
*/
QueryRunner qr = new QueryRunner();
String sql = "INSERT INTO product (pname,price) VALUES(?,?)";
Object[] params = {"桃子", 2.23};
int row = qr.update(conn, sql, params);
System.out.println(row);
DbUtils.closeQuietly(conn);
}
public static void query() {
conn = getConnection();
/**
* 创建QueryaRunner类对象
* 插入
*/
QueryRunner qr = new QueryRunner();
/**
* 查询
*/
List al = null;
try {
al = qr.query(conn, "select * from product", new MapListHandler());
} catch (SQLException e) {
e.printStackTrace();
}
Iterator ite = al.iterator();
while (ite.hasNext()) {
Map map = (Map) ite.next();
System.out.println(map.get("pname"));
}
}
public static void modify() throws SQLException {
conn = getConnection();
QueryRunner qr = new QueryRunner();
String sql = "UPDATE product SET pname=?,price=? where ID=?";
//定义Object数组，存储？中的参数；
Object[] params = {"玫瑰", "2.5", 4};
int row = qr.update(conn, sql, params);
System.out.println(row);
DbUtils.closeQuietly(conn);
}
public static void delete() throws SQLException {
conn = getConnection();
QueryRunner qr = new QueryRunner();
String sql = "DELETE FROM product where Id=?";
int row = qr.update(conn, sql, 7);
System.out.println(row);
}
}
ResultSetHandler 结果处理类
| 类名 | 描述 |
|---|---|
| ArrayHandler | 将结果集合的第一条记录封装到一个Object[]数组中，数组中的每一个元素就是这条记录中每一个字段的值 |
| ArrayListHandler | 将结果集合的第一条记录封装到一个Object[]数组中,将这些数组封装到list数组中 |
| BeanHandler | 将结果集中第一条记录封装到指定的javaBean |
| BeanListHandler | 将结果集中每第一条记录封装到指定的javaBean中,将javaBean封装到list集合中 |
| ColumnListHandler | 将结果集中指定的列的字段值，封装到一个list集合中 |
| ScalarHandler | 它是用于单数据列。例如：select count（*）form 表操作 |
| MapListHandler |
package controller.commonsDButils;
import com.sun.xml.internal.ws.developer.MemberSubmissionEndpointReference;
import controller.domain.Product;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
public class QueryRunnerDemo1 {
private static Connection conn = QueryRunnerDemo.getConnection();
public static void main(String[] args) throws SQLException {
// ArrayHandler();
// ArrayListHandler();
// Beanandler();
// BeanListHandler();
// ColumnListHandler();
// ScalarHandler();
// MapHandler();
MapListHandler();
}
/**
* 结果集的第八种方法 MapListHandler
* 将结果集中的每一行数据存储到Map集合中
* Map<健，值> 健：列名 值：这列的数据
* 存储到List
*/
public static void MapListHandler() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql = "SELECT * FROM company.product";
List <Map <String, Object>> list = qr.query(conn, sql, new MapListHandler());
for (Map <String, Object> map : list) {
for (String i : map.keySet()) {
Object value = map.get(i);
System.out.print(i + "..." + value + " ");
}
System.out.println();
}
}
/**
* 结果集的第七种方法 MapHandler
* 将结果集中的第一行数据，封装到map集合中
* Map<健，值> 健：列名 值：这列的数据
*/
public static void MapHandler() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql = "SELECT * FROM company.product";
Map <String, Object> map = qr.query(conn, sql, new MapHandler());
for (String i : map.keySet()) {
Object value = map.get(i);
System.out.println(i + "..." + value);
}
}
/**
* 结果集的第六种方法 ScalarHandler
* 对于查询只处理一种结果
*/
public static void ScalarHandler() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql = "SELECT count(*) FROM company.product";
Object count = qr.query(conn, sql, new ScalarHandler <Object>());
System.out.println(count);
}
/**
* 结果集的第五种方法 ColumnListHandler
* 结果集，指定列是的数据，存储到List集合
* List<object> 每个列数据类型不同
*/
public static void ColumnListHandler() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql = "SELECT * FROM company.product";
List <Object> list = qr.query(conn, sql, new ColumnListHandler <Object>("pname"));
for (Object obj : list) {
System.out.println(obj);
}
}
/**
* 结果集的第四种方法 BeanListHandler
* 将结果的每一行，封装成JavaBean对象中
* 对象存储到List数组中
*/
public static void BeanListHandler() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql = "SELECT * FROM company.product";
List <Product> list = qr.query(conn, sql, new BeanListHandler <Product>(Product.class));
for (Product objs : list) {
System.out.println(objs.toString());
}
DbUtils.close(conn);
}
/**
* 结果集的第三种方法 Beanandler
* 将结果的第一行，封装到javaBean
*/
public static void Beanandler() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql = "SELECT * FROM company.product";
Product product = qr.query(conn, sql, new BeanHandler <Product>(Product.class));
System.out.println(product);
DbUtils.close(conn);
}
/**
* 结果集的第二种方法 ArrayListHandler
* 将结果的每一行，封装到对象数组中 出现很多对象数组
* 对象存储到List数组中
*/
public static void ArrayListHandler() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql = "SELECT * FROM company.product";
List <Object[]> result = qr.query(conn, sql, new ArrayListHandler());
for (Object[] objs : result) {
for (Object obj : objs) {
System.out.print(obj + "\t");
}
System.out.println();
}
DbUtils.close(conn);
}
/**
* 结果集的第一种方法 ArrayHandler
* 将结果的第一行，封装到数组中 Object【】
*/
public static void ArrayHandler() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql = "SELECT * FROM company.product";
Object[] result = qr.query(conn, sql, new ArrayHandler());
for (Object obi : result) {
System.out.print(obi + "\t");
}
DbUtils.close(conn);
}
}
写一个JDBCUtils的工具类，高效的连接数据库 JDBCUtils类&& QueryRunnerDemo
- JDBCUtils类
package controller.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;
public class JDBCUtils {
private static BasicDataSource basicDataSource = new BasicDataSource();
// 静态代码块，对basicDataSource对象中配置
static {
basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
basicDataSource.setUrl("jdbc:mysql://localhost:3306/company?characterEncoding=utf8&useSSL=true");
basicDataSource.setUsername("root");
basicDataSource.setPassword("root");
// 连接池中的连接数
basicDataSource.setInitialSize(10);
basicDataSource.setMaxIdle(8);
basicDataSource.setMinIdle(1);
}
// 定义静态方法，返回basicDataSource对象
public static BasicDataSource getBasicDataSource() {
return basicDataSource;
}
}
- QueryRunnerDemo测试类
package controller.DataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import java.sql.SQLException;
import java.util.List;
public class QueryRunnerDemo {
public static void main(String[] args) {
// insert();
query();
}
private static QueryRunner qr = new QueryRunner(JDBCUtils.getBasicDataSource());
public static void insert() {
String sql = "INSERT INTO product (pname,price) VALUES(?,?)";
Object[] params = {"人参果", 11.23};
try {
int row = qr.update(sql, params);
System.out.println(row);
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void query() {
String sql = "SELECT * FROM product";
try {
List <Object[]> result = qr.query(sql, new ArrayListHandler());
for (Object[] objs : result) {
for (Object obj : objs) {
System.out.print(obj + "\t");
}
System.out.println();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
