import java.sql.*;
import java.util.ArrayList;
import java.util.List;
class Student {
private int id;
private String name;
private int age;
public Student(int id, String name, int age) {
super();
this.id = id;
this.name = name;
this.age = age;
}
public Student() {
}
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;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", age=" + age + "]";
}
}
class StudentDao {
Connection con;
PreparedStatement pst;
ResultSet rs;
public Connection getConn() throws ClassNotFoundException, SQLException
{
// 1 加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 2 获得数据库的连接
Connection con =
DriverManager.getConnection
("jdbc:mysql://localhost:3306/rot?useUnicode=true&characterEncoding=utf-8", "root", "");
return con;
}
public void closeAll() throws SQLException
{
if(rs!=null)
{
rs.close();
}
if(pst!=null)
{
pst.close();
}
}
//增加
public int addStudent(Student student) throws ClassNotFoundException, SQLException
{
con=getConn();
String insertSQL="insert into student values(null,?,?)";
pst = con.prepareStatement(insertSQL);
pst.setString(1,student.getName());
pst.setInt(2, student.getAge());
int result=pst.executeUpdate();
closeAll();
return result;
}
//删除
public int delStudent(int id) throws ClassNotFoundException, SQLException
{
con=getConn();
String sql="delete from student where id=?";
pst = con.prepareStatement(sql);
pst.setInt(1,id);
int result=pst.executeUpdate();
pst.close();
con.close();
return result;
}
//修改
public int updateStudent(Student student) throws ClassNotFoundException, SQLException
{
con=getConn();
String sql="update student set name=?,age=? where id=?";
pst = con.prepareStatement(sql);
pst.setString(1,student.getName());
pst.setInt(2, student.getAge());
pst.setInt(3,student.getId());
int result=pst.executeUpdate();
pst.close();
con.close();
return result;
}
//查询所有记录
public List<Student> queryAll() throws ClassNotFoundException, SQLException
{
con=getConn();
String sql="select * from student";
pst=con.prepareStatement(sql);
rs = pst.executeQuery();
List<Student> stuList=new ArrayList<Student>();
while(rs.next())
{
Student stu=new Student();
stu.setId(rs.getInt(1));
stu.setName(rs.getString(2));
stu.setAge(rs.getInt(3));
stuList.add(stu);
}
return stuList;
}
//根据主键查询记录
public Student queryById(int id) throws ClassNotFoundException, SQLException
{
con=getConn();
String sql="select * from student where id=?";
pst=con.prepareStatement(sql);
pst.setInt(1, id);
rs = pst.executeQuery();
Student stu=null;
if(rs.next())
{
stu=new Student(rs.getInt(1),rs.getString(2),rs.getInt(3));
}
return stu;
}
}
public class Main {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
StudentDao dao=new StudentDao();
Student stu1=new Student(1,"系统",22);
int result = dao.addStudent(stu1);
if(result==1)
{
System.out.println("插入记录成功!");
}
int result2 = dao.delStudent(1);
if(result2==1)
{
System.out.println("删除记录成功!");
}
Student stu2=new Student(3,"李四",32);
int result3 = dao.updateStudent(stu2);
if(result3==1)
{
System.out.println("修改记录成功!");
}
List<Student> stuList=dao.queryAll();
for(Student stu:stuList)
{
System.out.println(stu);
}
System.out.println("=======");
Student stu = dao.queryById(100);
System.out.println(stu);
}
}
import java.util.ArrayList;
import java.util.List;
class Student {
private int id;
private String name;
private int age;
public Student(int id, String name, int age) {
super();
this.id = id;
this.name = name;
this.age = age;
}
public Student() {
}
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;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", age=" + age + "]";
}
}
class StudentDao {
Connection con;
PreparedStatement pst;
ResultSet rs;
public Connection getConn() throws ClassNotFoundException, SQLException
{
// 1 加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 2 获得数据库的连接
Connection con =
DriverManager.getConnection
("jdbc:mysql://localhost:3306/rot?useUnicode=true&characterEncoding=utf-8", "root", "");
return con;
}
public void closeAll() throws SQLException
{
if(rs!=null)
{
rs.close();
}
if(pst!=null)
{
pst.close();
}
}
//增加
public int addStudent(Student student) throws ClassNotFoundException, SQLException
{
con=getConn();
String insertSQL="insert into student values(null,?,?)";
pst = con.prepareStatement(insertSQL);
pst.setString(1,student.getName());
pst.setInt(2, student.getAge());
int result=pst.executeUpdate();
closeAll();
return result;
}
//删除
public int delStudent(int id) throws ClassNotFoundException, SQLException
{
con=getConn();
String sql="delete from student where id=?";
pst = con.prepareStatement(sql);
pst.setInt(1,id);
int result=pst.executeUpdate();
pst.close();
con.close();
return result;
}
//修改
public int updateStudent(Student student) throws ClassNotFoundException, SQLException
{
con=getConn();
String sql="update student set name=?,age=? where id=?";
pst = con.prepareStatement(sql);
pst.setString(1,student.getName());
pst.setInt(2, student.getAge());
pst.setInt(3,student.getId());
int result=pst.executeUpdate();
pst.close();
con.close();
return result;
}
//查询所有记录
public List<Student> queryAll() throws ClassNotFoundException, SQLException
{
con=getConn();
String sql="select * from student";
pst=con.prepareStatement(sql);
rs = pst.executeQuery();
List<Student> stuList=new ArrayList<Student>();
while(rs.next())
{
Student stu=new Student();
stu.setId(rs.getInt(1));
stu.setName(rs.getString(2));
stu.setAge(rs.getInt(3));
stuList.add(stu);
}
return stuList;
}
//根据主键查询记录
public Student queryById(int id) throws ClassNotFoundException, SQLException
{
con=getConn();
String sql="select * from student where id=?";
pst=con.prepareStatement(sql);
pst.setInt(1, id);
rs = pst.executeQuery();
Student stu=null;
if(rs.next())
{
stu=new Student(rs.getInt(1),rs.getString(2),rs.getInt(3));
}
return stu;
}
}
public class Main {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
StudentDao dao=new StudentDao();
Student stu1=new Student(1,"系统",22);
int result = dao.addStudent(stu1);
if(result==1)
{
System.out.println("插入记录成功!");
}
int result2 = dao.delStudent(1);
if(result2==1)
{
System.out.println("删除记录成功!");
}
Student stu2=new Student(3,"李四",32);
int result3 = dao.updateStudent(stu2);
if(result3==1)
{
System.out.println("修改记录成功!");
}
List<Student> stuList=dao.queryAll();
for(Student stu:stuList)
{
System.out.println(stu);
}
System.out.println("=======");
Student stu = dao.queryById(100);
System.out.println(stu);
}
}