java 連接 mysql

防止注入攻擊，使用第二種方法

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語(yǔ)句判斷正確還是失敗
        String sql = "SELECT * FROM login where account = '" + use + "' and password = '" + pass + "'";
        /**
         * 控制臺(tái)輸入 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();



        /**
         * 防止注入攻擊
         * 有一個(gè)子接口preparedStatement(String sql)
         * sql 語(yǔ)句全部用占位符 ？
         */
        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 封裝的這個(gè)包
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類對(duì)象
         * 插入
         */
        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類對(duì)象
         * 插入
         */
        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數(shù)組，存儲(chǔ)？中的參數(shù)；
        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 結(jié)果處理類
類名 描述
ArrayHandler 將結(jié)果集合的第一條記錄封裝到一個(gè)Object[]數(shù)組中，數(shù)組中的每一個(gè)元素就是這條記錄中每一個(gè)字段的值
ArrayListHandler 將結(jié)果集合的第一條記錄封裝到一個(gè)Object[]數(shù)組中,將這些數(shù)組封裝到list數(shù)組中
BeanHandler 將結(jié)果集中第一條記錄封裝到指定的javaBean
BeanListHandler 將結(jié)果集中每第一條記錄封裝到指定的javaBean中,將javaBean封裝到list集合中
ColumnListHandler 將結(jié)果集中指定的列的字段值，封裝到一個(gè)list集合中
ScalarHandler 它是用于單數(shù)據(jù)列。例如：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();
    }

    /**
     * 結(jié)果集的第八種方法 MapListHandler
     * 將結(jié)果集中的每一行數(shù)據(jù)存儲(chǔ)到Map集合中
     * Map<健，值> 健：列名 值：這列的數(shù)據(jù)
     * 存儲(chǔ)到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();
        }
    }

    /**
     * 結(jié)果集的第七種方法 MapHandler
     * 將結(jié)果集中的第一行數(shù)據(jù)，封裝到map集合中
     * Map<健，值> ?。毫忻?值：這列的數(shù)據(jù)
     */
    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);
        }
    }


    /**
     * 結(jié)果集的第六種方法 ScalarHandler
     * 對(duì)于查詢只處理一種結(jié)果
     */

    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);
    }

    /**
     * 結(jié)果集的第五種方法 ColumnListHandler
     * 結(jié)果集，指定列是的數(shù)據(jù)，存儲(chǔ)到List集合
     * List<object> 每個(gè)列數(shù)據(jù)類型不同
     */

    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);
        }
    }

    /**
     * 結(jié)果集的第四種方法 BeanListHandler
     * 將結(jié)果的每一行，封裝成JavaBean對(duì)象中
     * 對(duì)象存儲(chǔ)到List數(shù)組中
     */
    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);
    }

    /**
     * 結(jié)果集的第三種方法 Beanandler
     * 將結(jié)果的第一行，封裝到j(luò)avaBean
     */
    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);
    }

    /**
     * 結(jié)果集的第二種方法 ArrayListHandler
     * 將結(jié)果的每一行，封裝到對(duì)象數(shù)組中  出現(xiàn)很多對(duì)象數(shù)組
     * 對(duì)象存儲(chǔ)到List數(shù)組中
     */
    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);
    }


    /**
     * 結(jié)果集的第一種方法 ArrayHandler
     * 將結(jié)果的第一行，封裝到數(shù)組中  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);
    }
}
寫(xiě)一個(gè)JDBCUtils的工具類，高效的連接數(shù)據(jù)庫(kù) JDBCUtils類&& QueryRunnerDemo
  • JDBCUtils類
package controller.DataSource;

import org.apache.commons.dbcp2.BasicDataSource;

public class JDBCUtils {
    private static BasicDataSource basicDataSource = new BasicDataSource();

    // 靜態(tài)代碼塊，對(duì)basicDataSource對(duì)象中配置
    static {
        basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
        basicDataSource.setUrl("jdbc:mysql://localhost:3306/company?characterEncoding=utf8&useSSL=true");
        basicDataSource.setUsername("root");
        basicDataSource.setPassword("root");
        // 連接池中的連接數(shù)
        basicDataSource.setInitialSize(10);
        basicDataSource.setMaxIdle(8);
        basicDataSource.setMinIdle(1);
    }
    // 定義靜態(tài)方法，返回basicDataSource對(duì)象

    public static BasicDataSource getBasicDataSource() {
        return basicDataSource;
    }
}
  • QueryRunnerDemo測(cè)試類
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();
        }
    }
}
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容