一、实体类
package jdbc_0825.com.enity;
public class T_Students {
private int s_id;
private String s_name;
private int s_age;
public T_Students(){
}
public T_Students(int s_id, String s_name, int s_age) {
this.s_id = s_id;
this.s_name = s_name;
this.s_age = s_age;
}
public int getS_id() {
return s_id;
}
public void setS_id(int s_id) {
this.s_id = s_id;
}
public String getS_name() {
return s_name;
}
public void setS_name(String s_name) {
this.s_name = s_name;
}
public int getS_age() {
return s_age;
}
public void setS_age(int s_age) {
this.s_age = s_age;
}
@Override
public String toString() {
return "T_Students:" +
"s_id=" + s_id +
", s_name='" + s_name + '\'' +
", s_age=" + s_age;
}
}
二、连接类
package jdbc_0825.com.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBUtil {
static Connection con;
static{
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//创建连接
public static Connection getConnection() throws SQLException {
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/class?","root","root");
return con;
}
//关闭连接
public static void closeConnection() throws SQLException {
con.close();
}
}
/**
- 1.是否有返回值,看后面还会用到它吗
- 2.是否是静态:直接类名.方法就可以引用
- 3.是否私有:看是否只在本类中使用
- **/
三、业务类
package jdbc_0825.com.service;
import jdbc_0825.com.enity.T_Students;
import jdbc_0825.com.util.DBUtil;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
public class DBService {
String sql;
PreparedStatement ps;
List<T_Students> list = new ArrayList<T_Students>();
ResultSet res;
Scanner input = new Scanner(System.in);
//增
public void insert() throws SQLException {
sql="insert into students value(?,?,?)";
ps=DBUtil.getConnection().prepareStatement(sql);
System.out.println("请输入您要插入的id,姓名以及年龄:");
int id= input.nextInt();
String name = input.next();
int age = input.nextInt();
ps.setInt(1,id);
ps.setString(2,name);
ps.setInt(3,age);
ps.executeUpdate();
System.out.println("插入成功!");
ps.close();
}
//删所有
public void delete() throws SQLException {
sql="delete * from students";
ps=DBUtil.getConnection().prepareStatement(sql);
ps.execute();
ps.close();
}
//根据id删
public void deletebyId(int id) throws SQLException {
sql="delete from students where s_id=?";
ps=DBUtil.getConnection().prepareStatement(sql);
ps.setInt(1,id);//setInt()就是给占位符设置值的
ps.executeUpdate();//执行
System.out.println("删除成功!");
ps.close();
}
//改
public void update(String name,int id) throws SQLException {
sql="update students set s_name =? where s_id =?";
//编译
ps=DBUtil.getConnection().prepareStatement(sql);
ps.setString(1,name);
ps.setInt(2,id);
System.out.println("更改成功!");
ps.executeUpdate();
ps.close();
}
//查所有
public List select() throws SQLException {
sql="select * from students";
//编译
ps = DBUtil.getConnection().prepareStatement(sql);
//执行
res = ps.executeQuery();
//把表中的每一列对应实体的属性
T_Students ts = new T_Students();
while(res.next()){
ts.setS_id(res.getInt("s_id"));
ts.setS_name(res.getString("s_name"));
ts.setS_age(res.getInt("s_age"));
list.add(ts);
}
res.close();
//ps.close();
return list;
}
//根据id查
public T_Students selectbyId(int id ) throws SQLException {
sql = "select * from students where s_id=1";//sql语句
ps = DBUtil.getConnection().prepareStatement(sql);//编译
res = ps.executeQuery();//执行
T_Students ts = new T_Students();
while(res.next()){
if(res.getInt("s_id")==id) {
ts.setS_id(res.getInt("s_id"));
ts.setS_name(res.getString("s_name"));
ts.setS_age(res.getInt("s_age"));
}
}
res.close();
ps.close();
return ts;
}
}
四、测试类
package jdbc_0825.com.test;
import jdbc_0825.com.enity.T_Students;
import jdbc_0825.com.service.DBService;
import jdbc_0825.com.util.DBUtil;
import java.sql.SQLException;
import java.util.List;
public class Test {
public static void main(String args[]) throws SQLException {
//连接
DBUtil.getConnection();
//执行
DBService ser = new DBService();
List<T_Students> list = ser.select();
System.out.println(list);
ser.insert();
//关闭连接
DBUtil.closeConnection();
}
}