jdbc连接数据库

package com.jd.util.db;

import java.sql.ResultSet;

public interface IRowMapper {
    void mapRow(ResultSet resultSet);
}





package com.jd.util.db;

import java.sql.*;

public class DBUtil {

    static {
        //1、加载驱动类
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取MySQL连接
     *
     * @autor Liu
     */
    private  static  Connection getConnection() throws  SQLException{
        String url = PropertiesUtil.value("url");
        String userName = PropertiesUtil.value("userName");
        String password = PropertiesUtil.value("password");
        return DriverManager.getConnection(url,userName,password);
    }
    public static void select(String sql, IRowMapper rowMapper){
        Connection connection =null;
        Statement statement =null;
        ResultSet resultSet =null;
        try {
            //2、获取MySQL连接
            connection = getConnection();
            //3、创建语句
            statement = connection.createStatement();
            //4、执行SQL语句
            resultSet = statement.executeQuery(sql);
            //5、处理结果
            rowMapper.mapRow(resultSet);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {//6、释放资源
           close(resultSet,statement,connection);
        }
    }

    public  static  boolean exist(String sql){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //2、获取数据库资源
            connection = getConnection();
            //3、创建Statement实例
            statement = connection.createStatement();
            //4、执行SQL语句
            resultSet = statement.executeQuery(sql);
            //5、处理结果
            //next用于移动指针并判断当前指针所在的位置是否有数据
            return  resultSet.next();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {//6、释放资源
            close(resultSet,statement,connection);
        }
        return  false;
    }
    public  static  boolean exist(String sql,Object...objects){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //2、获取数据库资源
            connection = getConnection();
            //3、创建Statement实例
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < objects.length; i++) {
                preparedStatement.setObject(i+1,objects[i]);
            }
            //4、执行SQL语句
            resultSet = preparedStatement.executeQuery();
            //5、处理结果
            //next用于移动指针并判断当前指针所在的位置是否有数据
            return  resultSet.next();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {//6、释放资源
            close(resultSet,preparedStatement,connection);
        }
        return  false;
    }

    private  static void close(Statement statement,Connection connection){
        if(statement!=null) {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(connection!=null) {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    private  static void close(ResultSet resultSet,Statement statement,Connection connection){
        if(resultSet!=null) {
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(statement!=null) {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(connection!=null) {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    private  static void close(ResultSet resultSet,PreparedStatement preparedStatement,Connection connection){
        if(resultSet!=null) {
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(preparedStatement!=null) {
            try {
                preparedStatement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(connection!=null) {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    public static void select(String sql, IRowMapper rowMapper, Object... objects) {
        String url = "jdbc:mysql://127.0.0.1:3306/vensus?useSSL=false";
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            //2、获取连接
            connection = DriverManager.getConnection(url, "root", "root");
            //jdbc创建六大过程，sql注入，怎么解决，怎么开始
            //           //3、创建语句 （速度快，但会出现sql注入）
//            Statement statement  =connection.createStatement();
//            //4、执行SQL
//            String sql = "select id from user_info where user_name='"+userName+"'and password = '"+password+"'";
//            System.out.println(sql);
//            ResultSet resultSet = statement.executeQuery(sql);
            //速度慢，但是防止sql注入

            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < objects.length; i++) {
                preparedStatement.setObject(i + 1, objects[i]);
            }
           resultSet = preparedStatement.executeQuery();
//            preparedStatement.setObject(1,userName);
//            preparedStatement.setObject(2,password);
            rowMapper.mapRow(resultSet);

        } catch (SQLException e) {
            e.printStackTrace();
        }//6、释放资源
        finally {
            close(resultSet,preparedStatement,connection);
        }
    }

    public static boolean update(String sql) {
        Connection connection = null;
        Statement statement = null;
        String url = "jdbc:mysql://127.0.0.1:3306/vensus?useSSL=false";
        try {
            //2、连接MySQL数据库 vensus
            connection = getConnection();
            //3、创建SQL语句
            statement = connection.createStatement();
            //4、执行SQL
            int effect = statement.executeUpdate(sql);
            //5、处理结果
            return effect > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6、释放资源
          close(statement, connection);
        }
        return false;
    } 
    public static boolean update(String sql,Object... objects) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        String url = "jdbc:mysql://127.0.0.1:3306/vensus?useSSL=false";
        try {
            //2、连接MySQL数据库 vensus
            connection = getConnection();
            //3、创建SQL语句
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < objects.length; i++) {
                preparedStatement.setObject(i+1,objects[i]);
            }
            //4、执行SQL
            int effect = preparedStatement.executeUpdate();
            //5、处理结果
            return effect > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6、释放资源
        close(preparedStatement, connection);
        }
        return false;
    }
    public static boolean transaction(String... sqls) {
        Connection connection =null;
        Statement statement = null;
        try {
            //2、获取连接
            connection = getConnection();
            connection.setAutoCommit(false);
            //3、创建语句
            statement =  connection.createStatement();
            //4、执行语句，现在是一条条执行，
            for (String sql:sqls) {
                statement.executeUpdate(sql);
            }
            connection.commit();
            //6、释放资源
            statement.close();
            connection.close();
            return  true;
        } catch (SQLException throwables) {
            if (connection!=null) {
                try {
                    connection.rollback();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            throwables.printStackTrace();
        }finally {
            close(statement, connection);
        }
        return false;
    }
    public static boolean batch(String... sqls) {
        //String url = "jdbc:mysql://127.0.0.1:3306/sys?useSSL=false";
        Connection connection = null;
        Statement statement = null;
        try {
            //2、获取连接
            connection = getConnection();
            //取消自动提交，不出错才提交，否则回滚
            connection.setAutoCommit(false);
            //3、创建语句
            statement =  connection.createStatement();
            //4、执行语句,现在全部一起执行
            for (String sql:sqls) {
                statement.addBatch(sql);
            }
            statement.executeBatch();
            connection.commit();
            //6、释放资源
            statement.close();
            connection.close();
            return  true;
        } catch (SQLException throwables) {
            if (connection!=null) {
                try {
                    connection.rollback();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            throwables.printStackTrace();
        }finally {
            close(statement, connection);
        }
        return false;
    }
}

package com.jd.util.db;

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

public class PropertiesUtil {

    private  static Properties properties = new Properties();

    static {
        InputStream inputStream = PropertiesUtil.class.getClassLoader().getResourceAsStream("db.properties");
        try {
            properties.load(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

//    public static void main(String[] args) {
//        String url = properties.getProperty("url");
//        System.out.println(url);
//    }

    //alt+crtl+v
    public static String value(String key) {
        return properties.getProperty(key);
    }
}

package com.jd.util;

import java.util.UUID;

public class StringUtil {

    public  static  String getId(){
        return UUID.randomUUID().toString();
    }
}

db.properties

url=jdbc:mysql://127.0.0.1:3306/vensus?useSSL=false
user=root
password=root

Test
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class Test4 {
    public static void main(String[] args) {
        String sql1 = "update account set money = money -100 where card_id = '1234567890'";
        String sql2 = "update account set money = money +100 where cardd = '0987654321'";
       //处理结果
//        if (transaction(sql1,sql2)){
//            System.out.println("成功");
//            return;
//        }
//        System.out.println("失败");
        if (batch(sql1,sql2)){
            System.out.println("成功");
            return;
        }
        System.out.println("失败");
    }

    public static boolean transaction(String... sqls) {
        //1、加载驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        String url = "jdbc:mysql://127.0.0.1:3306/sys?useSSL=false";
        Connection connection =null;
        try {
            //2、获取连接
            connection = DriverManager.getConnection(url, "root", "root");
            connection.setAutoCommit(false);
            //3、创建语句
            Statement statement =  connection.createStatement();
            //4、执行语句，现在是一条条执行，
            for (String sql:sqls) {
                statement.executeUpdate(sql);
            }
            connection.commit();
            //6、释放资源
            statement.close();
            connection.close();
            return  true;
        } catch (SQLException throwables) {
            if (connection!=null) {
                try {
                    connection.rollback();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            throwables.printStackTrace();
        }
        return false;
    }
    public static boolean batch(String... sqls) {
        //1、加载驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        String url = "jdbc:mysql://127.0.0.1:3306/sys?useSSL=false";
        Connection connection =null;
        try {
            //2、获取连接
            connection = DriverManager.getConnection(url, "root", "root");
           //取消自动提交，不出错才提交，否则回滚
            connection.setAutoCommit(false);
            //3、创建语句
            Statement statement =  connection.createStatement();
            //4、执行语句,现在全部一起执行
            for (String sql:sqls) {
                statement.addBatch(sql);
            }
            statement.executeBatch();
            connection.commit();
            //6、释放资源
            statement.close();
            connection.close();
            return  true;
        } catch (SQLException throwables) {
            if (connection!=null) {
                try {
                    connection.rollback();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            throwables.printStackTrace();
        }
        return false;
    }
}

import com.jd.util.StringUtil;
import com.jd.util.db.DBUtil;
import org.apache.commons.codec.digest.DigestUtils;

//尽量少用数据库的方法，用Java里的
//加密
public class Test1 {
    public static void main(String[] args) {
//        String userName = "张三";
//        String password = "admin";
//        String sql="insert into user_info(id,user_name,password,birthday date,mobile)values(uuid(),?,MD5(?),?,?)";
//        if(DBUtil.update(sql,userName,password)){
//            System.out.println("ok");
//        }else{
//            System.out.println("no");
//        }
//        String sql1 = "select id from user_info where user_name = ? and password = md5(?)";
//        if (DBUtil.exist(sql1,userName,password)){
//            System.out.println("YES");
//            return;
//        }
//        System.out.println("NO");
//        String string = StringUtil.getId();
//        System.out.println(string);
//        String str = DigestUtils.md5Hex("123456");
//        System.out.println(str);
    }
}