Java JDBC学习
1、数据库连接Java
JDBC是Java写的,谁连接谁不都一样么(狗头)
连接逻辑(个人理解请勿较真):
-
MySQL自报家门要连接Java
-
管理部门把MySQL的信息输入系统,创建连接
-
返回一个创建的操作对象
public class JDBCTest { private static final String URL = "jdbc:mysql://localhost:3306/jdbc_study?useSSL=false"; private static final String USERNAME = "root"; private static final String PASSWORD = "cadx520"; public static void main(String[] args) throws Exception{ // 我MySQL数据库要连接Java,先报上名字 Class.forName("com.mysql.jdbc.Driver"); // 管理部门(java)创建一个连接的机会 Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD); // 返回一个操作对象statement Statement statement = connection.createStatement(); // 下面就可以操作数据库了! ResultSet resultSet = statement.executeQuery("select * from admin;"); while (resultSet.next()){ int anInt = resultSet.getInt(1); String string = resultSet.getString(2); int anInt1 = resultSet.getInt(3); System.out.println("id:"+anInt+",name:"+string+",age:"+anInt1); } // 记得关闭连接对象 statement.close(); connection.close(); } } /*结果: id:1,name:zyy,age:21 id:2,name:frank,age:32 */
返回值resultSet有get方法,即有顺序可以得到数据,更像是LinkedHashSet!
2、规范代码
public class JDBCTest { private static final String URL = "jdbc:mysql://localhost:3306/jdbc_study?useSSL=false"; private static final String USERNAME = "root"; private static final String PASSWORD = "cadx520"; private static final String DRIVER = "com.mysql.jdbc.Driver"; private static Connection connection; private static Statement statement; private static ResultSet resultSet; public static void main(String[] args) { try{ int i = 1; Class.forName(DRIVER); connection = DriverManager.getConnection(URL, USERNAME, PASSWORD); statement = connection.createStatement(); resultSet = statement.executeQuery("select * from admin;"); while (resultSet.next()) { int id = resultSet.getInt(1); String name = resultSet.getString(2); int age = resultSet.getInt(3); System.out.println("第" + (i++) + "个人: " + "id->" + id + ",name->" + name + ",age ->" + age); } } catch (Exception e){ e.printStackTrace(); } finally { try { resultSet.close(); statement.close(); connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
结果:
第1个人: id->1,name->张耀元,age ->21 第2个人: id->2,name->frank,age ->32
解释:
-
连接数据库不建议使用throw,推荐使用try/catch/finally
-
常量建议提前定义,符合开发规范
-
connection、statement、resultSet提前定义是为了配合try/catch/finally,只有提前定义了finally里面才能获取到!
3、JDBCUtils封装
每次操作数据库都要写这么长的代码属实不方便,因此将连接数据库的方式封装成一个工具类,直接调用就爽多了!
-
将常量写到配置文件里。这样减少对代码的修改(不能让客户去修改代码吧!),换数据库或者url只需要修改配置文件即可。
db.properties
url=jdbc:mysql://localhost:3306/jdbc_study?useSSL=false&characterEncoding=utf8 username=root password=cadx520 driver=com.mysql.jdbc.Driver
useSSL=false:关闭SSL连接
characterEncoding=utf8:
因为数据库的字符集是utf-8,所以要设置字符编码为utf8,否则插入到数据库的中文字符会乱码,同样只有编译器的字符编码为utf-8,从数据库读出的中文字符才不会乱码!注意,记得将配置文件所在的文件夹设置为Resources Root,这样加载配置文件只需要写文件名即可。
public class JDBCUtils { private static String driver; private static String username; private static String password; private static String url; private JDBCUtils() { } static { try { InputStream stream = ClassLoader.getSystemResourceAsStream("db.properties"); Properties properties = new Properties(); properties.load(stream); driver = properties.getProperty("driver"); username = properties.getProperty("username"); password = properties.getProperty("password"); url = properties.getProperty("url"); } catch (Exception e) { e.printStackTrace(); } } public static Connection getConnection() throws SQLException, ClassNotFoundException { Class.forName(driver); return DriverManager.getConnection(url,username,password); } public static void close(Connection connection, Statement statement) throws SQLException { if(statement != null){ statement.close(); } if(connection != null){ connection.close(); } } public static void close(Connection connection, Statement statement, ResultSet resultSet) throws SQLException { if(resultSet != null){ resultSet.close(); } if(statement != null){ statement.close(); } if(connection != null){ connection.close(); } } }
代码解释:
-
用到的加载配置文件的方法步骤(个人理解):
-
ClassLoader.getSystemResourceAsStream("db.properties"),就是把系统的资源变成一个流引进来,这步工作是ClassLoader做的
-
变成一个流之后,谁要使用这个流,自己去加载!于时properties.load(stream),加载没有返回参数就意味着本身就是参数
-
于是,把自己作为一个map集合通过getKey的方式得到配置文件的内容:username = properties.getProperty("username")
-
-
利用单例设计模式,将构造方法private,给一个静态的public方法得到connection对象,这里抛出了异常,那么调用这个方法同样要解决异常(使用try/catch包裹)
-
同样的操作设计一个close方法依次关闭操作对象。
-
使用JDBCUtils增删改举例:
public class JDBCTest { public Connection connection; public Statement statement; // 删除、修改只需要改SQL语句即可! @Test public void insertTest(){ try { connection = JDBCUtils.getConnection(); statement = connection.createStatement(); String sql = "insert into admin(name,age) values('zyy','21');"; int i = statement.executeUpdate(sql); if (i>0){ System.out.println("insert success!"); } } catch (Exception e){ e.printStackTrace(); } finally { try { JDBCUtils.close(connection,statement); } catch (SQLException e) { e.printStackTrace(); } } } }
4、改用PrepareStatement和问号占位
PrepareStatement比较安全,可以防注入,所以重新修改代码,以后只用prepareStatement
public class JDBCUtils { private static String driver; private static String username; private static String password; private static String url; private JDBCUtils() { } static { try { InputStream stream = ClassLoader.getSystemResourceAsStream("db.properties"); Properties properties = new Properties(); properties.load(stream); driver = properties.getProperty("driver"); username = properties.getProperty("username"); password = properties.getProperty("password"); url = properties.getProperty("url"); } catch (Exception e) { e.printStackTrace(); } } public static Connection getConnection() throws Exception { Class.forName(driver); return DriverManager.getConnection(url,username,password); } public static void close(Connection connection, PreparedStatement preparedStatement) throws SQLException { if(preparedStatement != null){ preparedStatement.close(); } if(connection != null){ connection.close(); } } public static void close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) throws SQLException { if(resultSet != null){ resultSet.close(); } if(preparedStatement != null){ preparedStatement.close(); } if(connection != null){ connection.close(); } } }
preparedStatement的逻辑是:先把sql语句预编译一下,其中sql语句中的数据库具体数据使用?代替,采用set的方式给出数据。然后再执行,其他操作与statement一样。
public class JDBCTest { public Connection connection; public PreparedStatement preparedStatement; public ResultSet resultSet; @Test public void insertTest(){ try { connection = JDBCUtils.getConnection(); String sql = "insert into admin(name,age) values (?,?)"; preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1,"张耀元"); preparedStatement.setInt(2,21); int i = preparedStatement.executeUpdate(); if (i>0){ System.out.println("insert success!"); } } catch (Exception e){ e.printStackTrace(); } finally { try { JDBCUtils.close(connection,preparedStatement); } catch (SQLException e) { e.printStackTrace(); } } } @Test public void queryTest(){ try{ connection = JDBCUtils.getConnection(); String sql = "select * from admin;"; preparedStatement = connection.prepareStatement(sql); resultSet = preparedStatement.executeQuery(); while (resultSet.next()){ int id = resultSet.getInt(1); String name = resultSet.getString(2); int age = resultSet.getInt(3); System.out.println(name); } } catch (Exception e){ e.printStackTrace(); } finally { try { JDBCUtils.close(connection,preparedStatement,resultSet); } catch (SQLException e) { e.printStackTrace(); } } } }
此外,prepareStatement还有一个很大的优点,就是SQL语句没有写死,这就意味着更加灵活的操作SQL语句,直接在set里面设置参数,就可以从其他地方传入参数(就像是一个方法一样!),比如我接收用户的输入作为参数:
public class JDBCTest { public static Connection connection; public static PreparedStatement preparedStatement; public static Scanner scanner; public static void main(String[] args){ try { connection = JDBCUtils.getConnection(); String sql = "insert into admin(name,age) values (?,?)"; String name; Integer age = null; boolean flag = true; preparedStatement = connection.prepareStatement(sql); System.out.println("请输入要插入的数据,姓名:"); scanner = new Scanner(System.in); name = scanner.nextLine(); System.out.println("请输入要插入的数据,年龄:"); // 用户只能输入数字 do { try { age = scanner.nextInt(); flag = false; } catch (Exception e){ // 把上一条的不规范数据删去(相当于指针下移) scanner.next(); System.out.println("请输入数字:"); } }while (flag); preparedStatement.setString(1,name); preparedStatement.setInt(2,age); int i = preparedStatement.executeUpdate(); if (i>0){ System.out.println("insert success!"); } } catch (Exception e){ e.printStackTrace(); } finally { scanner.close(); try { JDBCUtils.close(connection,preparedStatement); } catch (SQLException e) { e.printStackTrace(); } } } }
解释:do/while循环利用了异常,scanner.nextInt()方法只能接收int类型的输入,否则会报出异常,在catch里执行提示输出,继续循环直到输入数字。scanner.next()的作用相当于下移指针,否则scanner里面仍然是上一条不合规范的数据,则会一直执行catch语句,变成死循环!