//java之jdbc学生信息的增删改
//先建立学生类和成绩类
package USEBEAN;
public class Grade {
private int id;
private int grade;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getGrade() {
return grade;
}
public void setGrade(int grade) {
this.grade = grade;
}
}
//—————————————
package USEBEAN;
public class Student {
private int id;
private String name;
private int age;
private int grade;
public int getGrade() {
return grade;
}
public void setGrade(int grade) {
this.grade = grade;
}
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;
}
}
//——————-连接数据库
package USEUTIL;
import java.sql.Connection;
import java.sql.DriverManager;
public class BaseConnection {
public static Connection getConnection() {
Connection conn=null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection("jdbc:mysql://127.0.0.1/student_table","root","1211");
}catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static void main(String[] args) {
Connection conn=BaseConnection.getConnection();
System.out.println(conn);
}
}
//——————–泛型DAO+JDBC增删改
package USEDAO;
import java.sql.Connection;
import java.util.ArrayList;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import USEUTIL.BaseConnection;
public class StudentDao<Student> {
public ArrayList<Student> getList(){
ArrayList<Student> ar=new ArrayList<Student>();
Connection conn=BaseConnection.getConnection();
PreparedStatement ps=null;
ResultSet rs=null;
try {
String sql="select * from every_stu,stu_kind where every_stu.id=stu_kind.id";
ps=conn.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()) {
USEBEAN.Student st=new USEBEAN.Student();
st.setId(rs.getInt("id"));
st.setName(rs.getString("name"));
st.setAge(rs.getInt("age"));
st.setGrade(rs.getInt("grade"));
ar.add((Student) st);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if(rs!=null) {
rs.close();
}
if(ps!=null) {
ps.close();
}
if(conn!=null) {
conn.close();
}
}catch(Exception ee) {
ee.printStackTrace();
}
}
return ar;
}
public void insert(Student st) {
Connection conn=BaseConnection.getConnection();
PreparedStatement ps=null;
String sql="insert into every_stu(name,age) "+"values(?,?)";
try {
ps=conn.prepareStatement(sql);
ps.setString(1,((USEBEAN.Student) st).getName());
ps.setInt(2,((USEBEAN.Student) st).getAge());
int a=ps.executeUpdate();
if(a>0) {
System.out.println("添加成功");
}else {
System.out.println("添加失败");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if(ps!=null) {
ps.close();
}
if(conn!=null) {
conn.close();
}
}catch(Exception ee) {
ee.printStackTrace();
}
}
}
public void update(Student st) {
Connection conn=BaseConnection.getConnection();
PreparedStatement ps=null;
String sql="update every_stu set name=?,age=?"+" where id=?";
try {
ps=conn.prepareStatement(sql);
ps.setString(1,((USEBEAN.Student) st).getName());
ps.setInt(2,((USEBEAN.Student) st).getAge());
ps.setInt(3,((USEBEAN.Student) st).getId());
int a=ps.executeUpdate();
if(a>0) {
System.out.println("更新成功");
}else {
System.out.println("更新失败");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if(ps!=null) {
ps.close();
}
if(conn!=null) {
conn.close();
}
}catch(Exception ee) {
ee.printStackTrace();
}
}
}
public void delete(int id) {
Connection conn=BaseConnection.getConnection();
PreparedStatement ps=null;
String sql="delete from every_stu where id=?";
try {
ps=conn.prepareStatement(sql);
ps.setInt(1,id);
int a=ps.executeUpdate();
if(a>0) {
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if(ps!=null) {
ps.close();
}
if(conn!=null) {
conn.close();
}
}catch(Exception ee) {
ee.printStackTrace();
}
}
}
}
//——————控制台实现信息的增删改
package USEMAIN;
import java.util.ArrayList;
import java.util.Scanner;
import USEBEAN.Student;
import USEDAO.StudentDao;
public class TestMain {
public static void main(String[] args) {
Scanner sc=new Scanner(System.in);
StudentDao std=new StudentDao();
while(true) {
System.out.println("1、查看学生信息 2、添加学生信息 3、删除学生信息 4、修改学生信息 5、退出" );
int a=sc.nextInt();
if(a==1) {
ArrayList<Student> ar=std.getList();
System.out.println("ID NAME AGE GRADE");
for(Student st:ar) {
System.out.println(st.getId()+"\t"+st.getName()+"\t"+st.getAge()+"\t"+st.getGrade());
}
}else if(a==2) {
System.out.println("请输入name age");
Student st=new Student();
st.setName(sc.next());
st.setAge(sc.nextInt());
std.insert(st);
}else if(a==3) {
System.out.println("请输入ID");
std.delete(sc.nextInt());
}else if(a==4) {
System.out.println("请输入name age ID");
Student st=new Student();
st.setName(sc.next());
st.setAge(sc.nextInt());
st.setId(sc.nextInt());
std.update(st);
}else if(a==5) {
return ;
}
}
}
}