JDBC固定步骤:
加载驱动
String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT";
String username="root";
String password="123456";
Class.forName("com.mysql.cj.jdbc.Driver");//这里不知道为什么加载com.mysql.jdbc.Driver会报错,有知道的大佬请留言
连接数据库,代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
向数据库发送SQL的对象Statement: CRUD
Statement statement = connection.createStatement();
编写SQL (根据业务, 不同的SQL)
String str1="select * from users";
String str2="insert into users values (4,"赵六","145667","werwef.@eq",current_time) ,(5,"田七","53234","fsd@df",current_time)";
String str3="delete from users where id=5";
String str4="update users set password="987654" where id=4";
执行SQL
// int i = statement.executeUpdate(str2);
// int i = statement.executeUpdate(str3);
// int i = statement.executeUpdate(str4);
ResultSet resultSet = statement.executeQuery(str1);
遍历结果集
while (resultSet.next()){
System.out.println("id:"+resultSet.getInt("id"));
System.out.println("name:"+resultSet.getString("name"));
System.out.println("password:"+resultSet.getString("password"));
System.out.println("email:"+resultSet.getString("email"));
System.out.println("birthday:"+resultSet.getString("birthday"));
}
关闭连接
resultSet.close();
statement.close();
connection.close();
-
statement.executeQuery(); //执行查询操作
-
statement.executeUpdate(); //执行增删改操作
-
resultset. beforeFirst(); // 移动到最前面
-
resu1tSet. afterlast(); //移动到最后面
-
resultset.next(); //移动到下一个数据
-
resultset. previous(); //移动到前一行
-
resu1tset. absolute(row); //移动到指定行
-
statement不安全使用prepareStatement 可以防SQL注入
以下是prepareStatement 的使用方法
public static void main(String[] args) throws ClassNotFoundException, SQLException {
String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT";
String username="root";
String password="123456";
//加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//连接数据库
Connection connection = DriverManager.getConnection(url, username, password);
//编写SQL
String str5="insert into users (id,name,password,email,birthday)values (?,?,?,?,?)";
//预编译
PreparedStatement ps = connection.prepareStatement(str5);
ps.setInt(1,6); //给第一个占位符?赋值6
ps.setString(2,"胡八"); //给第二个占位符?赋值"胡八"
ps.setString(3,"1223235"); //给第三个占位符?赋值”1223235“
ps.setString(4,"ew@12"); //给第四个占位符?赋值"ew@12"
ps.setDate(5,new Date(new java.util.Date().getTime()));
//给第五个占位符?赋值2020-05-19
//执行
int i = ps.executeUpdate();
if (i>0){
System.out.println("插入成功");
}
//关闭连接
ps.close();
connection.close();
}