JdbcUtil工具类实现连接mysql数据库

1，写一个JdbcUtil类，通过读取jdbc.properties文件内容来获取数据库连接。放在一个静态块内。 getConnection()方法获取Connection对象。close(ResultSet rs,Statement st, Connection con)方法来释放资源。代码如下：

package src.JdbcUtil;

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

public class JdbcUtil {
    private static String url;
    private static String user;
    private static String password;
    private static String driver;

    static {
        try {
            Properties p = new Properties();
            //通过ClassLoader获取对象，然后读取jdbc.properties进输入流。此时可以不用写绝对路径
            ClassLoader classLoader = JdbcUtil.class.getClassLoader();
            InputStream stream = classLoader.getResourceAsStream("jdbc.properties");
            p.load(stream);
            //p.load(new FileInputStream("D:\\ReflectDemo\\src\\main\\resources\\jdbc.properties"));
            url = p.getProperty("url");
            user = p.getProperty("user");
            password = p.getProperty("password");
            driver = p.getProperty("driver");
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //获取Connection对象，连接数据库
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    //释放资源
    public static void close(ResultSet rs, Statement st, Connection con) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

2：写一个jdbc.properties文件放在resources包下。代码如下：

url=jdbc:mysql://localhost/my1
user=root
password=root
driver=com.mysql.jdbc.Driver

 

 3：写测试类，调用JdbcUtil类，进行连接数据库并执行sql。（注意事物相关知识点）

 代码一：实现数据库的update 操作。

package src.jdbcUtilDemo;
import src.JdbcUtil.JdbcUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class jdbcUtilTest02 {
    public static void main(String[] args) {
        //初始化连接和PreparedStatement
        Connection con = null;
        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;
        try {
            //通过写的JdbcUtil工具类获取连接
            con = JdbcUtil.getConnection();
            //设置事物不自动提交
            con.setAutoCommit(false);
            //准备SQL
            String sql1 = "update account set money=money -? where name=?";
            String sql2 = "update account set money =money+? where name=?";
            ps1 = con.prepareStatement(sql1);
            ps2 = con.prepareStatement(sql2);
            ps1.setInt(1, 501);
            ps1.setString(2, "zhangsan");
            ps2.setInt(1, 501);
            ps2.setString(2, "lisi");
            //执行SQL
            ps1.executeUpdate();
            ps2.executeUpdate();
            //提交事务
            con.commit();
        } catch (Exception e) {//为了捕获异常，并回滚事物，此处应catch一个大点的异常
            try {
                if (con != null) {
                    con.rollback();//如果发生异常，回滚事物
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            //释放资源
            JdbcUtil.close(null, ps1, con);
            JdbcUtil.close(null, ps2, null);
        }
    }
}

代码一：实现数据库的select 操作。
package src.jdbcUtilDemo;

import src.JdbcUtil.JdbcUtil;

import java.sql.*;
import java.util.Scanner;

public class jdbcUtilDL {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        while (true) {
            System.out.println("请输入账户名");
            String name = sc.nextLine();
            System.out.println("请输入密码");
            String password = sc.nextLine();
            jdbcUtilDL jl = new jdbcUtilDL();
            boolean login = jl.login(name, password);
            if (login) {
                System.out.println("登录成功");
                break;
            } else {
                System.out.println("账户密码出错");
            }
        }

    }

    public boolean login(String name, String password) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        if (name == null || password == null) {
            return false;
        }
        try {
            con = JdbcUtil.getConnection();
            String sql = "select * from account1 where name=? and password=?";
            pst = con.prepareStatement(sql);
            pst.setString(1,name);
            pst.setString(2,password);
            rs = pst.executeQuery();
            return rs.next();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(rs, pst, con);
        }
        return false;
    }
}