学习笔记04（MySQL数据库做用户登陆）

             由于每次写程序都要更换数据库，而且每次的连接语句还有关闭资源操作都一样。所以一方面可以单独把连接和关闭资源写成两个方法，然后调用。另一方面，可以把连接用的参数放在一个配置文件里，每次使用数据库更改配置文件就可以了。

配置文件后缀名.properties

若是非web应用程序，建议放在src目录下。

文件内容如下：

           driverclass=com.mysql.jdbc.Driver
           url=jdbc:mysql://localhost:3306/库名
           username=root
           password=****

使用JDK提供的工具类ResourseBundle加载properties文件

      ResourceBundle rb=ResourceBundle.getBundle("properties文件名");

做了一个JDBC的例子

   项目的目录结构如下：

数据库如下：

dbinfo.properties文件内容如下（JDBCUtils_V2和JDBCUtils_V3都有调用）：

driver=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/day01?useUnicode=true&characterEncoding=utf-8username=rootpassword=051122

JDBCUtils_V1

package cn.itheima.jdbc;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;/* * 提供获取连接和释放资源的方法 * @version V1.0 * */public class JDBCUtils_V1 {/* * 获得连接方法*/public static Connection getConnection(){Connection conn=null;try {Class.forName("com.mysql.jdbc.Driver");    conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/day01","root","051122");} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}return conn;}public static void release(Connection conn,PreparedStatement pstmt,ResultSet rs){if(rs!=null){try {rs.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if(pstmt!=null){try {pstmt.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if(conn!=null){try {conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}

JDBCUtils_V2

package cn.itheima.jdbc;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ResourceBundle;/* * 提供获取连接和释放资源的方法 * @version V1.0 * */public class JDBCUtils_V2 {private static String driver;private static String url;private static String username;private static String password;/* * 静态代码块加载配置文件信息 * */static{ResourceBundle rb=ResourceBundle.getBundle("dbinfo");//对四个变量赋值driver=rb.getString("driver");url=rb.getString("url");username=rb.getString("username");password=rb.getString("password");}/* * 获得连接方法*/public static Connection getConnection(){Connection conn=null;try {Class.forName(driver);    conn=DriverManager.getConnection(url,username,password);} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}return conn;}public static void release(Connection conn,PreparedStatement pstmt,ResultSet rs){if(rs!=null){try {rs.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if(pstmt!=null){try {pstmt.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if(conn!=null){try {conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}
JDBCUtils_V3
package cn.itheima.jdbc;import java.io.IOException;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Properties;import java.util.ResourceBundle;/* * 提供获取连接和释放资源的方法 * @version V1.0 * */public class JDBCUtils_V3 {private static String driver;private static String url;private static String username;private static String password;/* * 静态代码块加载配置文件信息 * */static{try {//1.通过当前类获取类加载器ClassLoader classLoader=JDBCUtils_V3.class.getClassLoader();//2.通过类加载器的方法获得一个输入流InputStream is=classLoader.getResourceAsStream("dbinfo.properties");//3.创建一个properties对象Properties props=new Properties();//4.加载输入流props.load(is);//5.获取相关参数的值driver=props.getProperty("driver");url=props.getProperty("url");username=props.getProperty("username");password=props.getProperty("password");} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}/* * 获得连接方法*/public static Connection getConnection(){Connection conn=null;try {Class.forName(driver);    conn=DriverManager.getConnection(url,username,password);} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}return conn;}public static void release(Connection conn,PreparedStatement pstmt,ResultSet rs){if(rs!=null){try {rs.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if(pstmt!=null){try {pstmt.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if(conn!=null){try {conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}

测试类TestUtils
package cn.itheima.jdbc.test;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import org.junit.Test;import cn.itheima.jdbc.JDBCUtils_V1;import cn.itheima.jdbc.JDBCUtils_V2;import cn.itheima.jdbc.JDBCUtils_V3;/* * 测试工具类 * */public class TestUtils {/* * 更新操作，使用JDBCUtils_V3 * */@Testpublic void testUpdateById(){Connection conn=null;PreparedStatement pstmt=null;try {//1.获取连接conn = JDBCUtils_V3.getConnection();//2.填写sql语句String sql="update  users  set password=? where id=?";//3.获取执行sql语句对象pstmt=conn.prepareStatement(sql);//4.设置参数pstmt.setString(1,"1234");pstmt.setInt(2,6);//5.执行查询操作int row=pstmt.executeUpdate();if(row>0){System.out.println("更新成功！");}else{System.out.println("更新失败！");}} catch (Exception e) {// TODO: handle exceptionthrow new RuntimeException(e);}finally{//6.释放资源JDBCUtils_V3.release(conn, pstmt, null);}}/* * 删除用户信息，使用JDBCUtils_V3 **/   @Test   public void testDeleteById(){   Connection conn=null;PreparedStatement pstmt=null;try {//1.获取连接conn = JDBCUtils_V3.getConnection();//2.填写sql语句String sql="delete from users where id=?";//3.获取执行sql语句对象pstmt=conn.prepareStatement(sql);//4.设置参数pstmt.setInt(1, 5);//5.执行查询操作int row=pstmt.executeUpdate();if(row>0){System.out.println("删除成功！");}else{System.out.println("删除失败！");}} catch (Exception e) {// TODO: handle exceptionthrow new RuntimeException(e);}finally{//6.释放资源JDBCUtils_V3.release(conn, pstmt, null);}   }   /* *添加用户信息 JDBCUtils_V2 * */@Testpublic void testInsert(){Connection conn=null;PreparedStatement pstmt=null;try {//1.获取连接conn = JDBCUtils_V2.getConnection();//2.填写sql语句String sql="insert into users values(null,?,?)";//3.获取执行sql语句对象pstmt=conn.prepareStatement(sql);//4.设置参数pstmt.setString(1, "lisi");pstmt.setString(2, "1122");//5.执行查询操作int row=pstmt.executeUpdate();if(row>0){System.out.println("添加成功！");}else{System.out.println("添加失败！");}} catch (Exception e) {// TODO: handle exceptionthrow new RuntimeException(e);}finally{//6.释放资源JDBCUtils_V2.release(conn, pstmt, null);}}/* *根据id查询（JDBCUtils_V1） * */@Testpublic void testFindUserById(){Connection conn=null;PreparedStatement pstmt=null;ResultSet rs=null;try {//1.获取连接conn=JDBCUtils_V1.getConnection();//2.填写sql语句String sql="select * from users where id=?";//3.获取执行sql语句对象pstmt=conn.prepareStatement(sql);//4.设置参数pstmt.setInt(1, 1);//5.执行查询操作rs=pstmt.executeQuery();//6.处理结果集while(rs.next()){System.out.println("用户名"+rs.getString(2)+"密码"+rs.getString("password"));}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{//7.释放资源JDBCUtils_V1.release(conn, pstmt, rs);}}}