JDBC例2增删改查

package dao;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class Dao {
	
	private static String driver;
	private static String url;
	private static String user;
	private static String password;
	
	static{
		
		Properties prop=new Properties();
		Reader in;
		try {
			in=new FileReader("src\\config.properties");
			prop.load(in);
		} catch (Exception e) {
			
			e.printStackTrace();
		}
		driver=prop.getProperty("driver");
		url=prop.getProperty("url");
		user=prop.getProperty("user");
		password=prop.getProperty("password");
	}
	
	public static Connection open(){
		
		try {
			
			Class.forName(driver);
			return DriverManager.getConnection(url, user, password);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	
	public static void close(Connection conn){
		
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}

package entity;

public class Student {

	private int id;
	private String sno;
	private String name;
	private String gender;

	public int getId() {
		return id;
	}

	@Override
	public String toString() {
		return "Student [id=" + id + ", sno=" + sno + ", name=" + name + ", gender=" + gender + "]";
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getSno() {
		return sno;
	}

	public void setSno(String sno) {
		this.sno = sno;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getGender() {
		return gender;
	}

	public void setGender(String gender) {
		this.gender = gender;
	}

}

package entity;

public class Course {
	@Override
	public String toString() {
		return "Course [id=" + id + ", name=" + name + ", credit=" + credit + "]";
	}
	private int id;
	private String name;
	private int credit;
	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 getCredit() {
		return credit;
	}
	public void setCredit(int credit) {
		this.credit = credit;
	}
}

package view;

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 com.mysql.jdbc.PreparedStatement;

import dao.Dao;
import entity.Course;
import entity.Student;

public class Test {
	public static void main(String[] args) {
//		excute();
//		execute2();
//		createTable();
//		insert("zhangwu", 6);
		
//		Course course=new Course();
//		course.setName("english");
//		course.setCredit(4);
//		insert2(course);
		
//		Course c=new Course();
//		c.setId(4);
//		c.setName("abcd");
//		update(c);
		
//		del(2);
		
		Course c=query(4);
		if(c!=null)
		System.out.println(c.getId()+","+c.getName()+","+c.getCredit());
		
	}

	// 查询1
	 static void excute(){
	 Connection conn=Dao.open();
	 String sql="select * from student";
	 Statement stmt;
	 try {
	 stmt = conn.createStatement();
	 ResultSet rs=stmt.executeQuery(sql);
	
	 while(rs.next()){
	 int id=rs.getInt("id");
	 String sno=rs.getString("sno");
	 String name=rs.getString("name");
	 String gender=rs.getString("gender");
	 System.out.println(id+", "+sno+" ,"+name+", "+gender);
	 }
	 } catch (SQLException e) {
	 // TODO Auto-generated catch block
	 e.printStackTrace();
	 }
	 }

	// 新建表
//	static void createTable() {
//		Connection conn = Dao.open();
//		String sql = "create table newTable(id int primary key auto_increment,name varchar(20))";
//		Statement stmt;
//		try {
//			stmt = conn.createStatement();
//			stmt.execute(sql);
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//	}
	 
	 //查询2
	 static List<Student> execute2(){
		 List<Student>list=new ArrayList<Student>();
		 Connection conn=Dao.open();
		 String sql="select * from student";
		 Student st=null;
		 try {
			Statement stmt=conn.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			
			while(rs.next()){
				
			int id=rs.getInt("id");
			String sno=rs.getString("sno");
			 String name=rs.getString("name");
			 String gender=rs.getString("gender");
			// System.out.println(id+", "+sno+" ,"+name+", "+gender);
			st=new Student();
			st.setId(id);
			st.setSno(sno);
			st.setGender(gender);
			st.setName(name);
			
			list.add(st);
			
			}
			System.out.println(list);
		return list;
				
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

		}finally {
			try {
				Dao.close(conn);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	return null;
		 
	 }
	 
//增加1
	static void insert(String name,int credit){
		String sql="insert into course(name,credit)values(?,?)";
		Connection conn=Dao.open();
		PreparedStatement pstmt;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setInt(2, credit);
			pstmt.executeUpdate();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}finally {
			Dao.close(conn);
		}
	} 
	
	//增加2
	static void insert2(Course c){
		String sql="insert into course(name,credit)values(?,?)";
		Connection conn=Dao.open();
		PreparedStatement pstmt;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			pstmt.setString(1, c.getName());
			pstmt.setInt(2, c.getCredit());
			pstmt.executeUpdate();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}finally {
			Dao.close(conn);
		}
	} 
	
	//改
	static void update(Course c){
		String sql="update course set name=? where id=?";
		Connection conn=Dao.open();
		try {
			PreparedStatement pstmt=(PreparedStatement) conn.prepareStatement(sql);
			pstmt.setString(1, c.getName());
			pstmt.setInt(2, c.getId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			Dao.close(conn);
		}
	}
	
	//删除
	static void del(int id){
		String sql="delete from course where id=?";
		Connection conn=Dao.open();
		try {
			java.sql.PreparedStatement pstmt=conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//根据Id查询
	static Course query(int id){
		String sql="select id,name,credit from course where id=?";
		Connection conn=Dao.open();
		try {
			PreparedStatement pstmt=(PreparedStatement) conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			ResultSet rs=pstmt.executeQuery();
			
			if(rs.next()){
				String name=rs.getString("name");
				int credit=rs.getInt("credit");
				
				Course c=new Course();
				c.setId(id);
				c.setName(name);
				c.setCredit(credit);
				
				return c;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			Dao.close(conn);
		}
		return null;
	}
}