防止注入攻擊苹粟，使用第二種方法
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();
// 執(zhí)行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();
/**
* 創(chuàng)建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();
/**
* 創(chuàng)建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();
// 靜態(tài)代碼塊啥酱，對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);
}
// 定義靜態(tài)方法，返回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();
}
}
}
