Connection: 链接。作用就是从Java搭建一个桥梁到数据库。
Statement: SQL命令接口。作用就是从Java发送SQL语句到DBMS。如果是增删改语句,DBMS返回一个整数,表示影响的行数。
ResultSet: 结果集。 如果是查询语句,那么DBMS会返回一个虚拟的结果集。我们通过接口的方法就可以获取到结果集中的数据。
下面展示一个基于SQL的实例
package com;
import java.io.Serializable;
import java.util.Date;
public class User implements Serializable {
private int id;
private String name;
private int age;
private String sex;
private String address;
private Date birth;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Date getBirth() {
return birth;
}
public void setBirth(Date birth) {
this.birth = birth;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
", sex='" + sex + '\'' +
", address='" + address + '\'' +
", birth=" + birth +
'}';
}
}
package cn;
import com.User;
import java.util.List;
public interface Usercn {
public boolean save(User user);
public boolean update(User user);
public boolean delete(int id);
public User findone(int id);
public List<User> findAll();
}
package cn;
import com.User;
import org.JdbcUtils;
import java.util.List;
public class UsercnImpl implements Usercn{
@Override
public boolean save(User user) {
String sql = "insert into user values(?,?,?,?,?,?)";
return JdbcUtils.execute(sql,user.getId(),user.getName(),user.getAge(),user.getSex(),user.getAddress(),user.getBirth());
}
@Override
public boolean update(User user) {
String sql = "update user set name = ?,age = ?,sex = ?,address = ?,birth = ?where id = ?";
return JdbcUtils.execute(sql,user.getName(),user.getAge(),user.getSex(),user.getAddress(),user.getBirth(),user.getId());
}
@Override
public boolean delete(int id) {
String sql = "delete from user where id =" + id;
return JdbcUtils.execute(sql);
}
@Override
public User findone(int id) {
String sql = "select * from user where id = ?";
List<User> list = JdbcUtils.query(sql,id);
return list.isEmpty()?null:list.get(0);
}
@Override
public List<User> findAll() {
String sql = "select * from user";
return JdbcUtils.query(sql);
}
}
package org;
import com.User;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JdbcUtils {
private static Connection conn;
private static ResultSet rs;
private static PreparedStatement pstmt;
public static void getconn(){
try{
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/my?serverTimezone=UTC","root","root");
}catch(Exception e){
e.printStackTrace();
}
}
public static boolean execute(String sql,Object ...obj){
getconn();
try{
pstmt = conn.prepareStatement(sql);
for (int i = 0; i < obj.length; i++) {
pstmt.setObject(i+1,obj[i]);
}
int n = pstmt.executeUpdate();
return n > 0?true:false;
}catch(Exception e){
e.printStackTrace();
} finally {
close();
}
return false;
}
public static List<User> query(String sql, Object ...obj){
getconn();
List<User> list = new ArrayList<>();
try{
pstmt = conn.prepareStatement(sql);
for(int i = 0;i<obj.length;i++){
pstmt.setObject(i+1,obj[i]);
}
rs = pstmt.executeQuery();
while(rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setAge(rs.getInt("age"));
user.setSex(rs.getString("sex"));
user.setAddress(rs.getString("address"));
user.setBirth(rs.getDate("birth"));
list.add(user);
}
}catch(Exception e){
e.printStackTrace();
}finally {
close();
}
return list;
}
public static void close(){
try{
if (rs != null) {
rs.close();
}
if (pstmt != null){
pstmt.close();
}
if (conn != null){
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}
}
}
package test;
import cn.Usercn;
import cn.UsercnImpl;
import com.User;
import org.junit.Test;
import java.util.Date;
import java.util.List;
public class UsercnImplTest {
Usercn cn1 = new UsercnImpl();
@Test
public void testsave(){
User user = new User();
user.setId(2);
user.setName("凯影");
user.setAge(20);
user.setSex("男");
user.setAddress("北京");
user.setBirth(new Date());
cn1.save(user);
}
@Test
public void testUpdate(){
User user = new User();
user.setId(1);
user.setName("李嗣昭");
user.setAge(25);
user.setSex("男");
user.setAddress("西安");
user.setBirth(new Date());
cn1.update(user);
}
@Test
public void testFindAll(){
List<User> list = cn1.findAll();
list.stream().forEach(System.out::println);
}
@Test
public void testFindOne(){
User user = cn1.findone(1);
System.out.println(user);
}
}
根据在org包内的JdbcUtils中的
conn=DriverManager.getConnection(“jdbc:mysql://localhost:3306/my?serverTimezone=UTC”,“root”,“root”)语句,在Navicat for Mysql创建一个名为my的database,并在没有下创建一个新的查询,如下图所示
在运行test里的测试方法之前需要在没有里创建一个表,这里命名为user,首先要注意的是这里创建的表的属性与代码中创建的属性应该一致。
先创建表及其属性,再查看表如下图所示
这里在运行第一个测试方法(添加)
运行第二个测试方法(修改)
运行第三个测试方法(查看所有人的信息)
运行第四个测试方法(查看其中一个人的信息)