一、使用JDBC操作数据库的基本步骤
二、数据库
(一)数据库基础
1、select语句(用于查询数据表的数据)
2、insert语句(用于向数据表中插入新的数据)
3、update语句(用于修改数据表中的数据)
4、delete语句(用于删除数据表中的数据)
(二)连接数据库
连接数据库常见的错误:
3.用户名密码错误
4.驱动包的版本不兼容
案例康康:
public class Demo7 {
public static void main(String[] args) {
Connection con = null;
Statement sm = null;
ResultSet rs = null;
try {
//加载驱动包
Class.forName("com.mysql.jdbc.Driver");
//建立连接
con = DriverManager.getConnection("jdbc:mysql:///user","root","root");
//编写sql
String sql = "select * from user";
sm = con.createStatement();
rs = sm.executeQuery(sql);
while (rs.next()){
int id = rs.getInt("id");
String username = rs.getString("username");
String password = rs.getString("password");
System.out.println("编号:"+id+"\t"+"姓名:"+username+"\t"+"密码:"+password);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
if(rs != null) {
if (sm != null) {
if (con != null) {
try {
rs.close();
sm.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
}
}
(三)数据库查询
1、数据库查询
2、创建接口对象
3、ResultSet常用方法
(四)动态查询
1、你的SQL安全吗?
为了防止这种SQL注入,JDBC提供了动态查询
2、动态查询
案例分析:
public class Demo7 {
public static void main(String[] args) {
Connection con = null;
PreparedStatement sm = null;
ResultSet rs = null;
try {
//加载驱动包
Class.forName("com.mysql.jdbc.Driver");
//建立连接
con = DriverManager.getConnection("jdbc:mysql:///user","root","root");
//编写sql
String sql = "select * from user where username like ?";
sm = con.prepareStatement(sql);
sm.setString(1,"小%");
rs = sm.executeQuery();
while (rs.next()){
System.out.println("姓名:"+rs.getString(2)+"\t密码:"+rs.getString(3));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
if(rs != null) {
if (sm != null) {
if (con != null) {
try {
rs.close();
sm.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
}
}
3、添加、修改、删除记录
案例分析:
public class Demo7 {
public static void main(String[] args) {
Connection con = null;
Statement sm = null;
ResultSet rs = null;
try {
//加载驱动包
Class.forName("com.mysql.jdbc.Driver");
//建立连接
con = DriverManager.getConnection("jdbc:mysql:///user","root","root");
/*插入数据*/
String sql = "insert into user(username,password) values('赵亮亮','123')";
sm = con.createStatement();
int i = sm.executeUpdate(sql);
System.out.println(i);
/*修改数据*/
String sql1 = "update user set username = '赵丹' where id = 10";
sm = con.createStatement();
int j = sm.executeUpdate(sql1);
System.out.println(j);
/*删除数据*/
String sql2 = "delete from user where id = 10 ";
sm = con.createStatement();
int k = sm.executeUpdate(sql2);
System.out.println(k);
} catch (Exception e) {
e.printStackTrace();
}finally {
if(rs != null) {
if (sm != null) {
if (con != null) {
try {
rs.close();
sm.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
}
}