java编写连接mysql工具类

用jdbc连接mysql数据库时为了防止代码冗余，于是可以编写工具类

首先，线写一个配置文件

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456

 

username就用自己mysql的用户名，password也是

然后再去编写一个工具类

package com.gc.utils;

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


public class JdbcUtils {

    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static {

        try{
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");


            //1.驱动只用加载一次
            Class.forName(driver);
        } catch(IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接资源
     * */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }

    /**
     * 释放连接资源
     * 分别是Connection，Statement,ResultSet
     * */
    public static void release(Connection con, Statement st, ResultSet rs){

        if (rs!=null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (st!=null){
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(con!=null){
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

之后编写测试文件测试

增

package com.gc;

import com.gc.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Testinsert {
    public static void main(String[] args) {

        Connection conn = null;
        Statement st =null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection(); //获取数据库连接
            st = conn.createStatement();  //获取SQL执行对象
            String sql ="INSERT INTO USER(`id`,`name`,`password`,`email`,`birthday`)" +
                    "VALUE('1','miaomiao','123456','123456@qq.com','2001-01-01')";

            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            JdbcUtils.release(conn,st,rs);
        }
    }
}

删

package com.gc;

import com.gc.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestDelete {
    public static void main(String[] args) {

        Connection conn = null;
        Statement st =null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection(); //获取数据库连接
            st = conn.createStatement();  //获取SQL执行对象
            String sql ="DELETE FROM user WHERE id=1";

            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("删除成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            JdbcUtils.release(conn,st,rs);
        }
    }
}

改

package com.gc;

import com.g.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestUpdate {

    public static void main(String[] args) {

        Connection conn = null;
        Statement st =null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection(); //获取数据库连接
            st = conn.createStatement();  //获取SQL执行对象
            String sql ="\n" +
                    "UPDATE `user` SET `name`='xt' ,`email`='123456@qq.com' WHERE id=1";

            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("更新成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            JdbcUtils.release(conn,st,rs);
        }
    }
}

查

package com.gc;

import com.gc.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestSelect {
    public static void main(String[] args) {

        Connection conn = null;
        Statement st =null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection(); //获取数据库连接
            st = conn.createStatement();  //获取SQL执行对象
            String sql ="select * from user where id =1";

            rs = st.executeQuery(sql);

            while(rs.next()){
                String name=rs.getString("name");
                String email=rs.getString("email");
                System.out.println(name);
                System.out.println(email);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            JdbcUtils.release(conn,st,rs);
        }
    }
}

编写sql语句可以先去sqloyg编写语句测试

再复制到idea