JDBC连接数据库——进行增删改查（prepareStatement）

一、实体类

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();
}
}