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;	}}