第一天：java与mysql的连接工具类

　　　　　　　　　　　　　　　　　　　　　　　　　　第一天：java与mysql的连接工具类

java最新版马上就要收费，这无疑是这门语言的衰败起始，毕竟在中国收费便难发展，例如c#，但是毕业设计已经选好用java来写一个动态网站，

这已经是一个事实，还是得学，好在一法通万法通，不至于一无所获。

首先我们要把连接数据库的工具类写好，这里面无非就那么几个固定的对象、语句，

第一步，我们需要导包，进入maven随便选择一个版本下载就是，网址是https://mvnrepository.com/artifact/mysql/mysql-connector-java

然后把jar包复制到java动态网站下的lib文件夹里就是了，位置在/项目名/WebContent/WEB-INF/lib/mysql-connector-java-5.1.24-bin.jar

接下来就可以写连接类了，来个最简单的：

public class Jdbc {

    public static final String URL = "jdbc:mysql://localhost:3306/test";

    public static final String USER = "root";

    public static final String PWD = "123456";

    public static void main(String[] args) {

        //update();

        query();

    }

    public static void update() {

        Connection conn = null;

        Statement stmt =null;

        PreparedStatement pstmt = null;

        ResultSet rs = null;

        try {

            //加载驱动类

            Class.forName("com.mysql.jdbc.Driver");

            //与数据库建立连接

            conn = (Connection) DriverManager.getConnection(URL,USER,PWD);

            //执行sql

            //1.statement

            /*stmt = conn.createStatement();

            String sql = "insert into user values(2,'qzj',123) ";

            int count = stmt.executeUpdate(sql);*/

            //2.prepareStatement

            String sql = "insert into user values(?,?,?) ";

            pstmt = conn.prepareStatement(sql);

            pstmt.setInt(1, 3);

            pstmt.setString(2, "gg");

            pstmt.setInt(3, 22);

            int count = pstmt.executeUpdate();

            if(count > 0) {

                System.out.println("操作成功！");

            }

        } catch (ClassNotFoundException e) {

            e.printStackTrace();

        } catch (SQLException e) {

            e.printStackTrace();

        }catch (Exception e) {

            e.printStackTrace();

        }

        finally{

            try {

                if(stmt != null) stmt.close();

                if(conn != null) conn.close();

            } catch (SQLException e) {

                // TODO Auto-generated catch block

                e.printStackTrace();

            }

        }

    }

public static void query() {

        Connection conn = null;

        Statement stmt =null;

        PreparedStatement pstmt = null;

        ResultSet rs = null;

        try {

            //加载驱动类

            Class.forName("com.mysql.jdbc.Driver");

            //与数据库建立连接

            conn = (Connection) DriverManager.getConnection(URL,USER,PWD);

            //执行sql

            String sql = "select * from user";

            /*stmt = conn.createStatement();

            rs = stmt.executeQuery(sql);*/

            pstmt = conn.prepareStatement(sql);

            rs = pstmt.executeQuery();

            while(rs.next()) {

                int id = rs.getInt("id");

                String name = rs.getString("uname");

                String pwd = rs.getString("upwd");

                System.out.println(id+"--"+name+"--"+pwd);

            }

        } catch (ClassNotFoundException e) {

            e.printStackTrace();

        } catch (SQLException e) {

            e.printStackTrace();

        }catch (Exception e) {

            e.printStackTrace();

        }

        finally{

            try {

                if(stmt != null) stmt.close();

                if(conn != null) conn.close();

                if(rs != null) rs.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

}

基本上连接类中用到的就connection、statement、preparstatement、resultset四个对象，其中preparstatement是statement的子类，功能更多更好，

推荐优先使用preparstatement，理由如下：

1.编码更加简便（避免了字符串的拼接）

String name = "zs" ;

int age = 23 ;

stmt:

String sql =" insert into student(stuno,stuname) values('"+name+"',  "+age+" )    " ;

stmt.executeUpdate(sql);

pstmt:

String sql =" insert into student(stuno,stuname) values(?,?) " ;

pstmt = connection.prepareStatement(sql);//预编译SQL

pstmt.setString(1,name);

pstmt.setInt(2,age);

2.提高性能(因为有预编译操作，预编译只需要执行一次)

需要重复增加100条数

stmt:

String sql =" insert into student(stuno,stuname) values('"+name+"',  "+age+" )    " ;

for(100)

stmt.executeUpdate(sql);

pstmt:

String sql =" insert into student(stuno,stuname) values(?,?) " ;

pstmt = connection.prepareStatement(sql);//预编译SQL

pstmt.setString(1,name);

pstmt.setInt(2,age);

for( 100){

pstmt.executeUpdate();

}

3.安全（可以有效防止sql注入），何为sql注入，就是--将客户输入的内容和开发人员的SQL语句混为一体

stmt:存在被sql注入的风险

(例如输入用户名：任意值 ' or 1=1 --

密码：任意值)

分析：

当源代码像select count(*) from login where uname='"+name+"' and upwd ='"+pwd+"'

用户有心捣乱就可以进行sql注入，输入恶意用户名就变成了以下后果：

select count(*) from login where uname='任意值 ' or 1=1 --' and upwd ='任意值' ;//--是sql中的注释，后面语句被注释了

select count(*) from login where uname='任意值 ' or 1=1 ;//结果就变成了这样，因为or1=1，最终就正确了

select count(*) from login ;

pstmt:有效防止sql注入，推荐使用pstmt

除了以上直接输入连接信息外还可以写在一个/项目名/src/a.properties文件，再从a.properties取出来用，代码如下：

private static String url = null;

    private static String user = null;

    private static String password = null;

    private static String dv = null;

    static {

        Properties prop = new Properties();

        InputStream in = JdbcUtils.class.getResourceAsStream("/a.properties");

        try {

            prop.load(in);

            url = prop.getProperty("url");

            user = prop.getProperty("user");

            password= prop.getProperty("password");

            dv = prop.getProperty("driver");

            //加载驱动类

            try {

                Class.forName(dv);

            } catch (ClassNotFoundException e) {

                // TODO Auto-generated catch block

                e.printStackTrace();

            }

        } catch (Exception e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

    }

a.properties文件语句如下：

url:jdbc:mysql://localhost:3306/diary?characterEncoding=utf8

user:root

password:123456

driver:com.mysql.jdbc.Driver

上面只是最简单的也是最原始的连接工具类，再来个比较高级通用的：

package jdbc.util;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.sql.Connection;

//需要WebContent/WEB-INF/lib/mysql-connector-java-5.1.24-bin.jar

public class JdbcUtil {

    public static final String URL = "jdbc:mysql://localhost:3306/test";

    public static final String USER = "root";

    public static final String PWD = "123456";

    public static PreparedStatement pstmt = null ;

    public static Connection connection = null ;

    public static ResultSet rs = null ;

    //通用增删改

    public static boolean executeUpdate(String sql,Object[]params){

        try {

            pstmt = createPreParedStatement(sql,params);

            int count = pstmt.executeUpdate();

            if(count>0) {

                return true;

            }else {

                return false;

            }

        } catch (ClassNotFoundException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

            return false;

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

            return false;

        } catch (Exception e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

            return false;

        } finally {

            closeAll(null,pstmt,connection);

        }

    }

    //通用查

    public static ResultSet executeQuery(String sql,Object[]params)  {

        //Student student = null;

        //List<Student> students = new ArrayList<>();

        try {

            pstmt = createPreParedStatement(sql,params);

            rs = pstmt.executeQuery();

            return rs;

        } catch (ClassNotFoundException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

            return null;

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

            return null;

        }catch (Exception e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

            return null;

        }

    }

    //导入驱动，加载具体的驱动类

    public static Connection getConnection() throws ClassNotFoundException, SQLException {

        Class.forName("com.mysql.jdbc.Driver");

        return DriverManager.getConnection(URL,USER,PWD);

    }

    public static PreparedStatement createPreParedStatement(String sql,Object[] params) throws ClassNotFoundException, SQLException {

        pstmt = getConnection().prepareStatement(sql);

        if(pstmt != null) {

            for(int i=0;i<params.length;i++) {

                //循环赋值，类似pstmt.setint(1,id)

                pstmt.setObject(i+1, params[i]);

            }

        }

        return pstmt;

    }

    public static void closeAll(ResultSet rs,Statement stmt,Connection connection)

    {

        try {

            if(rs!=null)rs.close();

            if(pstmt!=null)pstmt.close();

            if(connection!=null)connection.close();

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

}

巴特西

第一天：java与mysql的连接工具类

最新文章

热门文章