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语句, 参数, 结果集解析等在代码中存在硬编码问题, 不易维护, 实际应用他们变化较大.