JDBC工具类和JDBC练习_登录案例
JDBC工具类
目的：简化书写
分析：
1.注册驱动
2.抽取一个方法获取连接对象
1）需求：不想传递参数（麻烦），还得保证工具类的通用性
2）解决：配置文件
jdbc.properties
url=
user=
password=
3.抽取一个方式释放资源
/* JDBC工具类 */ public class JDBCUtils { private static String url; private static String user; private static String password; private static String driver; /** * 文件读取，只需要读取一次就可拿到这些值。使用静态代码块 */ static { try { //读取资源文件，获取值 //创建Properties集合类： Properties pro = new Properties(); //获取src路径下的文件的方式 ---> ClassLoader 类加载器 ClassLoader classLoader = JDBCUtils.class.getClassLoader(); URL res = classLoader.getResource("jdbc.properties"); String path = res.getPath(); System.out.println(path); //加载文件 // pro.load(new FileReader("src/java/jdbc.properties")); pro.load(new FileReader(path)); //获取数据，赋值 url = pro.getProperty("url"); user = pro.getProperty("user"); password = pro.getProperty("password"); driver = pro.getProperty("driver"); //注册驱动 Class.forName(driver); } catch (IOException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } /** * 获取连接 * @return */ public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url,user,password); } /** * 释放资源 * @param stmt * @param conn */ public static void close(Statement stmt, Connection conn){ if (stmt!=null){ try { stmt.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (conn!=null){ try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } /** * 释放资源 * @param stmt * @param conn */ public static void close(ResultSet rs,Statement stmt, Connection conn){ if (rs!=null){ try { rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (stmt!=null){ try { stmt.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (conn!=null){ try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } }
测试：
public static void main(String[] args) throws Exception { List<Emp> list = new JDBCD01().findAll1(); System.out.println(list); System.out.println(list.size()); } /* 演示工具类 */ public List<Emp> findAll1(){ Connection conn = null; Statement stat = null; ResultSet rs = null; List<Emp> list = null; try { /*//注册驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //获取Connection对象 conn = DriverManager.getConnection("jdbc:mysql:///db2", "root", "root");*/ conn = JDBCUtils.getConnection(); //定义sql String sql = "select * from emp"; //获取执行sql的对象 Statement stat = conn.createStatement(); rs = stat.executeQuery(sql); Emp emp = null; list = new ArrayList<>(); while (rs.next()){ //获取数据 int id = rs.getInt("id"); String ename = rs.getString("ename"); int job_id = rs.getInt("job_id"); int mgr = rs.getInt("mgr"); Date joindate = rs.getDate("joindate"); double salary = rs.getDouble("salary"); double bonus = rs.getDouble("bonus"); int dept_id = rs.getInt("dept_id"); //创建emp对象,并赋值 emp = new Emp(); emp.setId(id); emp.setEname(ename); emp.setJob_id(job_id); emp.setMgr(mgr); emp.setJoindate(joindate); emp.setSalary(salary); emp.setBonus(bonus); emp.setDept_id(dept_id); list.add(emp); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally { JDBCUtils.close(rs,stat,conn); } return list; }
JDBC练习_登录案例
需求：
1.通过键盘录入用户名和密码
2.判断用户是否登录成功
select * from user where username = “” and password = “”;
如果这个sql有查询结果，则成功，反之则失败
public static void main(String[] args) { Scanner sc = new Scanner(System.in); System.out.println("输入用户名"); String username = sc.nextLine(); System.out.println("输入密码"); String password = sc.nextLine(); boolean flag = new JDBCD02().login(username, password); if (flag){ System.out.println("登录成功!"); }else { System.out.println("用户名和密码错误!"); } } /* 登录方法 创建JDBCDemo10类以及利用JDBCUtils工具类实现练习要求 */ public boolean login(String username,String password){ if (username==null || password==null){ return false; } //连接数据库是否登录成功 Connection conn = null; Statement stmt = null; ResultSet rs = null; try { //获取连接 conn = JDBCUtils.getConnection(); //定义sql String sql = "select * from user where username = '"+username+"' and password = '"+password+"' "; //获取执行sql对象 stmt = conn.createStatement(); rs = stmt.executeQuery(sql); return rs.next(); //如果有下一行，则返回true } catch (SQLException throwables) { throwables.printStackTrace(); }finally { JDBCUtils.close(rs,stmt,conn); } return false; }
工具类：
/* JDBC工具类 */ public class JDBCUtils { private static String url; private static String user; private static String password; private static String driver; /** * 文件读取，只需要读取一次就可拿到这些值。使用静态代码块 */ static { try { //读取资源文件，获取值 //创建Properties集合类： Properties pro = new Properties(); //获取src路径下的文件的方式 ---> ClassLoader 类加载器 ClassLoader classLoader = JDBCUtils.class.getClassLoader(); URL res = classLoader.getResource("jdbc.properties"); String path = res.getPath(); //加载文件 // pro.load(new FileReader("src/java/jdbc.properties")); pro.load(new FileReader(path)); //获取数据，赋值 url = pro.getProperty("url"); user = pro.getProperty("user"); password = pro.getProperty("password"); driver = pro.getProperty("driver"); //注册驱动 Class.forName(driver); } catch (IOException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } /** * 获取连接 * @return */ public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url,user,password); } /** * 释放资源 * @param stmt * @param conn */ public static void close(Statement stmt, Connection conn){ if (stmt!=null){ try { stmt.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (conn!=null){ try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } /** * 释放资源 * @param stmt * @param conn */ public static void close(ResultSet rs,Statement stmt, Connection conn){ if (rs!=null){ try { rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (stmt!=null){ try { stmt.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (conn!=null){ try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } }
