idea链接数据库

 第一部分

1.下载mysql对应版本的jar文件,我下载是mysql-connector-java-8.0.16.jar,地址

https://downloads.mysql.com/archives/c-odbc/

2.下载之后解压缩放到项目目录里面,记得右击文件夹add libray

 

 

 

 

 

 第二部分

以上配置就完成了,一下是代码

1.在src的目录下创建一个文件database.properties

user=root
password=88888888
driverClass=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql://localhost:3306/test_db

 

 

 2.创建一个类UntilJdbc,封装注册和释放数据的方法

public class UntilJdbc {

    private static String user;
    private static String password;
    private static String driverClass;
    private static String jdbcUrl;
    static {
        InputStream in = UntilJdbc.class.getClassLoader().getResourceAsStream("database.properties");
        Properties properties = new Properties();
        try {
            properties.load(in);

            user = properties.getProperty("user");
            password = properties.getProperty("password");
            driverClass = properties.getProperty("driverClass");
            jdbcUrl = properties.getProperty("jdbcUrl");

        } catch (IOException e) {
            e.printStackTrace();

        }
    }
    public static Connection getConn(){
        try {
            Class.forName(driverClass);
            Connection connection = DriverManager.getConnection(jdbcUrl,user,password);
            return connection;
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        }
       return null;
    }

    public static void closeSource(Connection connection){
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void closeSource(PreparedStatement preparedStatement, Connection connection){
        try {
            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
    public static void closeSource(ResultSet rs,PreparedStatement preparedStatement, Connection connection){
        try {
            rs.close();
            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

}

 数据库修改的方法

    private static void sqlUpdate(){
        Connection conn = UntilJdbc.getConn();
        /**
         * //Statement可以，但是容易造成sql注入，为了安全，所以尽量使用PreparedStatement
         */
        PreparedStatement stmt = null;
        // 获取执行对象Statement
        System.out.println(" 实例化Statement对象...");

        try {
            /*
            这里是开启事务
             */
//            conn.setAutoCommit(false);
            stmt = conn.prepareStatement("update students set name=? where id = ?");
            //update students set name=wang where id = 1
            stmt.setString(1,"wang");
            stmt.setInt(2,1);
            int rs = stmt.executeUpdate();
            if(rs == 1){
                System.out.println("这里是修改成功");
            }else{
                System.out.println("这里是修改失败");
            }
            /**
             * 这里是提交事务
             */
//            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            /**
             * 这里是回滚事务
             */
//            try {
//                if(conn != null) {
////                    conn.rollback();
//                }
//            } catch (SQLException e1) {
//                e1.printStackTrace();
//            }
        }finally {
            if(conn!= null && stmt!= null){
                UntilJdbc.closeSource(stmt,conn);
            }else{
                UntilJdbc.closeSource(conn);
            }
        }

    }

 数据库查询的方法

private static void sqlQuery(){
        Connection conn = UntilJdbc.getConn();
        /**
         * //Statement可以，但是容易造成sql注入，为了安全，所以尽量使用PreparedStatement
         */
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {

            stmt = conn.prepareStatement("select * from students where id >?");
            stmt.setInt(1,2);
            //select * fron students where id > 2
             rs = stmt.executeQuery();
            if (rs.next()){
                System.out.print("id:"+rs.getInt(1));
                System.out.print("  name:"+rs.getString("name"));
                System.out.print("  age:"+rs.getInt("age"));
                System.out.println("  hight:"+rs.getFloat("hight"));
                //id:3  name:赵  age:20  hight:1.52
                System.out.println("---------------------------------------------");

            }

        } catch (SQLException e) {
            e.printStackTrace();

        }finally {
            UntilJdbc.closeSource(rs,stmt,conn);
        }
    }