package com.zhidi.model;
public class Student {
private int id;
private String name;
private String banji;
private char sex;
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 String getBanji() {
return banji;
}
public void setBanji(String banji) {
this.banji = banji;
}
public char getSex() {
return sex;
}
public void setSex(char sex) {
this.sex = sex;
}
@Override
public String toString() {
return "学生 id=" + id + ", name=" + name + ", banji=" + banji + ", sex=" + sex ;
}
}
package com.zhidi.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBUtil {
private static final String URL = "jdbc:mysql://127.0.0.1:3306/person";
private static final String USER = "root";
private static final String PASSWORD = "root";
private static Connection conn = null;
static {
try {
//加载驱动程序
Class.forName("com.mysql.jdbc.Driver");
//获得数据库连接
conn = DriverManager.getConnection(URL, USER, PASSWORD);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConn() {
return conn;
}
}
package com.zhidi.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.zhidi.db.DBUtil;
import com.zhidi.model.Student;
public class StuDao {
private Connection conn = DBUtil.getConn();
// 添加
public void stuadd(Student stu) throws SQLException {
String sql = "insert into student (name,class,sex) values (?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, stu.getName());
ps.setString(2, stu.getBanji());
ps.setString(3, String.valueOf(stu.getSex()));
boolean b = ps.execute();
if (!b) {
System.out.println(stu.getName() + "创建成功");
} else {
System.out.println(stu.getName() + "创建失败");
}
}
// 删除
public void deleteStu(int id) throws SQLException {
String sql = "delete from Student where id =?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, id);
boolean b = ps.execute();
if (!b) {
System.out.println("删除成功");
} else {
System.out.println("删除失败");
}
}
// 修改
public void updateStu(Student stu) throws SQLException {
String sql = "update student set name=?,class=?,sex=? where id=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, stu.getName());
ps.setString(2, stu.getBanji());
ps.setString(3, String.valueOf(stu.getSex()));//char类型转为String类型
ps.setInt(4, stu.getId());
boolean b = ps.execute();
if (!b) {
System.out.println(stu.getName() + "修改成功");
} else {
System.out.println(stu.getName() + "修改失败");
}
}
// 查询一个学生
public Student selectStu(int id) throws SQLException {
String sql = "select * from student where id=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, id);
ResultSet rs = ps.executeQuery();
Student stu = null;
while (rs.next()) {
stu = new Student();
stu.setBanji(rs.getString("class"));
stu.setId(rs.getInt("id"));
stu.setName(rs.getString("name"));
String str = rs.getString("sex");
char[] c = str.toCharArray();//String类型转为char类型
for (char d : c) {
stu.setSex(d);
}
}
return stu;
}
// 查询所有学生
public List<Student> query() throws SQLException {
Statement sta = conn.createStatement();// 创建声明
ResultSet rs = sta.executeQuery("select * from student");
List<Student> list = new ArrayList<Student>();
Student stu = null;
while (rs.next()) {
stu = new Student();
stu.setBanji(rs.getString("class"));
stu.setId(rs.getInt("id"));
stu.setName(rs.getString("name"));
String str = rs.getString("sex");
char[] c = str.toCharArray();
for (char d : c) {
stu.setSex(d);
}
list.add(stu);
}
return list;
}
}
package com.zhidi.control;
import java.sql.SQLException;
import java.util.List;
import com.zhidi.dao.StuDao;
import com.zhidi.model.Student;
public class StuControl {
public static void main(String[] args) throws SQLException {
StuDao sd = new StuDao();
// 添加
Student stu = new Student();
stu.setName("yanyan");
stu.setSex('男');
stu.setBanji("四班");
sd.stuadd(stu);
// 删除
sd.deleteStu(5);
// 修改
Student stu1 = new Student();
stu1.setId(6);
stu1.setName("tomm");
stu1.setBanji("五班");
stu1.setSex('女');
sd.updateStu(stu1);
// 查询一个学生
Student stu2 = sd.selectStu(6);
System.out.println(stu2);
// 查询所有学生
List<Student> list = sd.query();
for (Student s : list) {
System.out.println(s.toString());
}
}
}