JDBC连接MySql对Student表的操作

数据实体 Student

package cn.itcast.mysql.student;
/*
 * 数据实体类
 */
public class Student {
	private int id;
	private String name;
	private String sex;
	private double grade;
	public Student(int id, String name, String sex, double grade) {
		
		this.id = id;
		this.name = name;
		this.sex = sex;
		this.grade = grade;
	}
	public Student() {
		// TODO Auto-generated constructor stub
	}
	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 getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public double getGrade() {
		return grade;
	}
	public void setGrade(double grade) {
		this.grade = grade;
	}
	@Override
	public String toString() {
		return "Student [id=" + id + ", name=" + name + ", sex=" + sex
				+ ", grade=" + grade + "]";
	}
}

数据访问对象 StudentDao

package cn.itcast.mysql.student;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cn.itcast.mysql.util.JDBCUtils;

/*
 * 对student表进行crud操作
 * dao(date access object) 数据访问对象
 * 一个表对应一个实现类
 */
public class StudentDao {
	 /*
	  * 保存一个学生的信息
	  */
	public void saveStudent(Student s)
	{
		Connection conn = null;
		Statement stmt = null;
		conn = JDBCUtils.getConnection();
		try {
			stmt = conn.createStatement();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		String sql = "INSERT INTO student(id,NAME,sex,grade) VALUE ("+s.getId()+",'"+s.getName()+"','"+s.getSex()+"','"+s.getGrade()+"')";
		try {
			int result = stmt.executeUpdate(sql);
			System.out.println("result = "+ result);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtils.closeResource(conn, stmt, null);
		}
	}
	 /*
	  * 更新指定学生的信息
	  */
	public void updateStudent(Student s)
	{
		Connection conn = null;
		Statement stmt = null;
		conn = JDBCUtils.getConnection();
		try {
			stmt = conn.createStatement();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		String sql = "UPDATE student SET NAME = '"+s.getName()+"',sex = '"+s.getSex()+"',grade = '"+s.getGrade()+"' WHERE id =  "+s.getId();
		try {
			int result = stmt.executeUpdate(sql);
			System.out.println(" update result = "+ result);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtils.closeResource(conn, stmt, null);
		}
		
	}
	/*
	 * 通过ID删除指定的表数据
	 */
	public void deleteStudentById(int id)
	{
		Connection conn = null;
		Statement stmt = null;
		conn = JDBCUtils.getConnection();
		try {
			stmt = conn.createStatement();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		String sql ="delete from student where id = "+id; 
		try {
			int result = stmt.executeUpdate(sql);
			System.out.println(" delete  result = "+ result);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtils.closeResource(conn, stmt, null);
		}
	}
	/*
	 * 删除表中所有记录
	 */
	public void deleteAllStudents()
	{
		Connection conn = null;
		Statement stmt = null;
		conn = JDBCUtils.getConnection();
		try {
			stmt = conn.createStatement();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		String sql = "delete from student";
		try {
			int result = stmt.executeUpdate(sql);
			System.out.println("delete all result = "+ result);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtils.closeResource(conn, stmt, null);
		}
	}
	/*
	 * 根据ID查询出所匹配的记录，返回student对象（可能为空 null）
	 */
	public Student findStudentById(int id)
	{
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		Student s = null;
		conn = JDBCUtils.getConnection();
		
		String sql = "select ID,NAME,sex,grade From student where id = 3";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if(rs.next())
			{
				s = new Student();
				s.setId(id);
				s.setName(rs.getString("name"));
				s.setSex(rs.getString("name"));
				s.setGrade(rs.getDouble("grade"));
			}else
			{
				//无执行语句
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtils.closeResource(conn, stmt, rs);
		}
		
		
		return s;
	}
	/*
	 * 查询得到表中所有数据，返回一个List
	 */
	public List<Student> findAllStudents()
	{
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
	
		List<Student> list = new ArrayList<Student>();
		conn = JDBCUtils.getConnection();
		
		String sql = "select id ,name,sex,grade From student";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next())
			{
				Student s = new Student();
				s.setId(rs.getInt("id"));
				s.setName(rs.getString("name"));
				s.setSex(rs.getString("sex"));
				s.setGrade(rs.getDouble("grade"));
				list.add(s);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
} 

测试类

package cn.itcast.mysql.student;

import java.util.List;

import org.junit.Test;

public class TestStudentDao {
	@Test
	public void TestSaveStudent(){
		StudentDao dao = new StudentDao();
		Student s = new Student(0, null, null, 0);
		s.setId(3);
		s.setName("da");
		s.setSex("男");
		s.setGrade(80);
		dao.saveStudent(s);
	}
	@Test
	public void TestUpdateStudent(){
		StudentDao dao = new StudentDao();
		Student s = new Student(0, null, null, 0);
		s.setId(2);
		s.setName("张三");
		s.setSex("女");
		s.setGrade(800);
		dao.updateStudent(s);
	}
	@Test
	public void TestDeleteStudentById(){
		StudentDao dao = new StudentDao();
		Student s = new Student(0, null, null, 0);
		int id = 3;
		dao.deleteStudentById(id);
	} 
	@Test
	public void TestDeleteAllStudent(){
		StudentDao dao = new StudentDao();
		dao.deleteAllStudents();
	}
	@Test
	public void testFindStudentById(){
		Student s = new Student();
		StudentDao dao = new StudentDao();
		s = dao.findStudentById(3);
		System.out.println(s);
	}
	@Test
	public void testFindAllStudent(){
		
		StudentDao dao = new StudentDao();
		List<Student> list  = dao.findAllStudents();
		for (Student s : list){
		//增强for循环，此处有错误
			System.out.println(s);
		}
	}
} 

 

 

转载于:https://my.oschina.net/u/1997259/blog/360562