JDBC入门

JDBC操作数据库的步骤:

1. 注册数据库驱动

2. 创建并获取数据库连接

3. 定义sql语句

4. 获得statement预处理对象

5. 执行sql语句,处理结果集

6. 释放资源

查询:

public static void main(String[] args) { Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { //加载数据库驱动 Class.forName("com.mysql.jdbc.Driver"); //通过驱动管理类获取数据库链接 connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8", "root", "root"); //定义sql语句 ?表示占位符 String sql = "select * from user where username = ?"; //获取预处理statement preparedStatement = connection.prepareStatement(sql); //设置参数，第一个参数为sql语句中参数的序号（从1开始），第二个参数为设置的参数值 preparedStatement.setString(1, "王五"); //向数据库发出sql执行查询，查询出结果集 resultSet = preparedStatement.executeQuery(); //遍历查询结果集 while(resultSet.next()){ System.out.println(resultSet.getString("id")+" "+resultSet.getString("username")); } } catch (Exception e) { e.printStackTrace(); }finally{ //释放资源 if(resultSet!=null){ try { resultSet.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(preparedStatement!=null){ try { preparedStatement.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(connection!=null){ try { connection.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }

---

插入:

public void testJDBC() throws Exception{ //1. 加载数据库驱动 Class.forName("com.mysql.jdbc.Driver"); //2. 创建并获取数据库链接 Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test1?characterEncoding=utf-8","root","123456"); //4. 设置sql语句 String sql = "insert into user_info( userName, userNo,studentId,studentName, isOnSchool, colorImage, colorLen, grayImage, grayLen, updateTime, permission) values(?, ?, ?, ?, ?, ?, ?, ?, ?,?,?)"; //3. 创建statement对象 PreparedStatement statement = connection.prepareStatement(sql); //5. 设置sql参数 statement.setString(1, "1"); statement.setString(2, "2"); statement.setString(3, "3"); statement.setString(4, "4"); statement.setInt(5, 5); statement.setString(6, "6"); statement.setInt(7, 7); statement.setString(8, "8"); statement.setInt(9, 9); statement.setString(10, "10"); statement.setInt(11, 11); statement.executeUpdate(); //8. 释放资源 statement.close(); connection.close(); }

JDBC操作数据库的缺点:

数据库连接的创建, 释放频繁造成系统资源浪费从而影响系统性能, 如果使用连接池可以解决此问题.sql语句, 参数, 结果集解析等在代码中存在硬编码问题, 不易维护, 实际应用他们变化较大.