需求:
实现用户名和密码存入数据库。
思路:
1、连接数据表用静态加载块
2、crud写成4个静态方法
具体流程:
from:https://www.tutorialspoint.com/jdbc/jdbc-sample-code.htm
参考:
1、jdbc:http://blog.csdn.net/williamchew/article/details/51951551
2、jdbc:http://www.cnblogs.com/wuyuegb2312/p/3872607.html
3、mysql:Mysql数据库中怎样快速查询符合条件的记录是否存在
4、select 1 的含义:https://zhidao.baidu.com/question/940814605173482052.html
5、判断resultset为空:http://bbs.csdn.net/topics/60427882
代码:
jdbc
1、intellij idea添加jar包,在Project Structure的dependencies里添加。
成功添加后external libraries是这样子的:
2、写代码前的准备--创建数据表
3、尝试
// import org.gjt.mm.mysql.*; 测试package是否正确导入 import java.sql.*; public class Database { }
public static void main(String[] args) { try { Class.forName("org.gjt.mm.mysql.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } }
Class是一个类,forName是这个类的一个静态方法。传入的string必须是某个类的全名,返回值为该类的对象。这里用这种方式加载驱动Driver的静态语句块。(为什么不可以用new Driver();的方式加载呢?)各种数据库对应的jar包、驱动类名和URL格式 补充:静态的两个特点:静态的只会在说明类的时候执行一次;不论创建多少对象静态的只有一份。
public static void main(String[] args) { try { Class.forName(JDBC_DRIVER); Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); // 可以理解为IO中的定位,传入URL可以推测getConnection里一定包装了关于Socket的操作 } catch (Exception e) { e.printStackTrace(); } }
可以观察到,不论是Driver或是Connection对象,都不是通过new来创建的。
// JDBC驱动名称和数据库位置 static final String JDBC_DRIVER = "org.gjt.mm.mysql.Driver"; static final String DB_URL = "jdbc:mysql://localhost:3306/qq"; // 用户名和密码 static final String USER = "root"; static final String PASS = "pass"; public static void main(String[] args) throws SQLException { Connection conn = null; Statement stmt = null; try { Class.forName(JDBC_DRIVER); conn = DriverManager.getConnection(DB_URL, USER, PASS); // 可以理解为IO中的定位,传入URL可以推测getConnection里一定包装了关于Socket的操作 stmt = conn.createStatement(); String sql = "insert user(username, password)values('red2', 'a123456')"; System.out.println(stmt.executeUpdate(sql)); } catch (Exception e) { e.printStackTrace(); } finally { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } }
升级update部分:
prep = conn.prepareStatement("insert user(username, password) values(? , ?)"); prep.setString(1, "hi"); prep.setString(2, "ss"); System.out.println(prep.executeUpdate());
融入主代码
package com.xkfx.qq.tools.serverOnly; import com.xkfx.qq.tools.transmit.User; import java.sql.*; public class Database { private static final String JDBC_DRIVER = "org.gjt.mm.mysql.Driver"; private static final String DB_URL = "jdbc:mysql://localhost:3306/qq"; private static final String USER = "root"; private static final String PASS = "pass"; private static Connection conn = null; static { try { Class.forName(JDBC_DRIVER); conn = DriverManager.getConnection(DB_URL, USER, PASS); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } public static void close() { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } public static void saveUser(User user) { PreparedStatement prep = null; try { prep = conn.prepareStatement("INSERT USER(username, password) VALUES(? , ?)"); prep.setString(1, user.getUsername()); prep.setString(2, user.getPassword()); prep.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { try { prep.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static User get(String username) { return null; } public static void updateUser(User user) { } public static void removeUser(User user) { PreparedStatement prep = null; try { prep = conn.prepareStatement("DELETE FROM USER WHERE username=?"); prep.setString(1, user.getUsername()); prep.execute(); } catch (SQLException e) { e.printStackTrace(); } finally { try { prep.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static boolean find(String username) { boolean flag = false; PreparedStatement prep = null; try { prep = conn.prepareStatement("SELECT 1 FROM USER WHERE username=?"); prep.setString(1, username); flag = prep.executeQuery().next(); } catch (SQLException e) { e.printStackTrace(); } finally { try { prep.close(); } catch (SQLException e) { e.printStackTrace(); } } return flag; } }