封装的一些数据库的方法，c3p0,增删改查等

封装的一些数据库的方法，c3p0,增删改查等

package cn.ygc.dao;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Map;

import com.mchange.v2.c3p0.ComboPooledDataSource;


import cn.ygc.modal.Result;
import cn.ygc.modal.SearchCredit;
import cn.ygc.modal.Student;
import cn.ygc.modal.Activity;
import cn.ygc.modal.Charts;
import cn.ygc.modal.GetClassStudents;
import cn.ygc.modal.GetCollegeTable;
//执行用户注册页

public class DBAgent {
	private static Connection con=null;
	private static Connection getConnection(){
		if(con==null){
			try {
				ComboPooledDataSource cpds = new ComboPooledDataSource();
				cpds.setDriverClass( "com.mysql.jdbc.Driver" ); //loads the jdbc driver            
				cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/ygc" );
				cpds.setUser("root");                                  
				cpds.setPassword("123456");  
				con=cpds.getConnection();
			} catch (PropertyVetoException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return con;
		
	}
	
	public static Student issetStudent(String id){
		Student p=new Student();
		String sql="select * from student where xh="+id+"";
		System.out.println(sql);
		Connection con=getConnection();
		try {
			ResultSet rs = con.createStatement().executeQuery(sql);
			while(rs.next()){
			p.setXh(rs.getInt("xh"));
			p.setDept(rs.getString("dept"));
			p.setMajor(rs.getString("major"));
			p.setOpenid(rs.getString("openid"));
			p.setXm(rs.getString("xm"));
		}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return p;
		}
	
	public static Activity GetActivity(String id){
		Activity p=new Activity();
		String sql="select * from activity where id="+id+"";
		Connection con=getConnection();
		try {
			ResultSet rs = con.createStatement().executeQuery(sql);
			while(rs.next()){
			p.setEndTime(rs.getString("endTime"));
			p.setStartTime(rs.getString("startTime"));
			p.setRegistStarttime(rs.getString("registStarttime"));
			p.setRegistEndtime(rs.getString("registEndtime"));
			p.setTitle(rs.getString("title"));
			p.setIntegral(Double.parseDouble(rs.getString("integral")));
			p.setContent(rs.getString("content"));
			p.setAddress(rs.getString("address"));
		}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return p;
		}
	
	
	public static boolean Saveopenid(String xh,String openid,String sfzh,String password,String phone){
		boolean r=false;
		Connection con=getConnection();
		String sql="update student set openid=?,sfzh=?,password=?,phone=? where xh=?";
		try {
			PreparedStatement pstm=con.prepareStatement(sql);
			pstm.setString(1, openid);
			pstm.setString(2, sfzh);
			pstm.setString(3, password);
			pstm.setString(4, phone);
			pstm.setString(5, xh);
			r=pstm.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return r;
		}
	
public static Result register(Map<String,String> map){
	Result rs=null;
	String sql=null;
	Connection con=getConnection();
	try {
		sql="insert into admin(username,realname,phone,password,email,img,typeid) values(?,?,?,?,?,?,?)";
		PreparedStatement pstm=con.prepareStatement(sql);
		pstm.setString(1, map.get("username"));
		pstm.setString(2, map.get("realname"));
		pstm.setString(3, map.get("phone")); 
		pstm.setString(4, map.get("password")); 
		pstm.setString(5, map.get("email")); 
		pstm.setString(6,map.get("filename"));
		pstm.setInt(7,Integer.parseInt(map.get("typeid")));
		pstm.execute();
	} catch (NumberFormatException e) {
		e.printStackTrace();
		return new Result(-2,"数据格式不对!!");
	} catch (SQLException e) {
		e.printStackTrace();
		return new Result(-3,"数据操作异常!!");
	}
	rs=new Result(0,"success");
	return rs;
	}

public static Result Issue(Map<String,String> map){
	Result rs=null;
	String sql=null;
	Connection con=getConnection();
	try {
		sql="insert into activity(title,content,registStarttime,registEndtime,thumbUrl,startTime,endTime,longitude,latitude,radius,types,address,toWhom,integral) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement pstm=con.prepareStatement(sql);
		pstm.setString(1, map.get("title"));
		pstm.setString(2, map.get("content"));
		pstm.setString(3, map.get("registStarttime")); 
		 pstm.setString(4, map.get("registEndtime")); 
		  pstm.setString(5, map.get("filename")); 
		pstm.setString(6,map.get("startTime"));
		pstm.setString(7, map.get("endTime")); 
		pstm.setDouble(8,Double.parseDouble(map.get("longitude")));
		 pstm.setDouble(9,Double.parseDouble(map.get("latitude")));
		pstm.setString(10,map.get("radius"));
		pstm.setInt(11,Integer.parseInt(map.get("types")));
		pstm.setString(12,map.get("address"));
		pstm.setString(13,map.get("toWhom"));
		pstm.setDouble(14,Double.parseDouble(map.get("integral")));
		pstm.execute();
	} catch (NumberFormatException e) {
		e.printStackTrace();
		return new Result(-2,"数据格式不对!!");
	} catch (SQLException e) {
		e.printStackTrace();
		return new Result(-3,"数据操作异常!!");
	}
	rs=new Result(0,"success");
	return rs;
}
	
	public static ArrayList<Activity> getActivities(String keywords){
		ArrayList<Activity> list=new ArrayList<Activity>();
		String sql;
		if(keywords==null || keywords==""){
			sql="select * from `activity` order by startTime desc";
		}
		else{
			sql="select * from `activity` where title like '%"+keywords+"%' or content like '%"+keywords+"%' order by startTime desc ";
			System.out.println(sql);
		}
		Connection con=getConnection();
		try {
			ResultSet rs = con.createStatement().executeQuery(sql);
			while(rs.next()){
			Activity p=new Activity();
			p.setTitle(rs.getString("title"));
			p.setContent(rs.getString("content"));
			p.setRegistStarttime(rs.getString("registStarttime"));
			p.setRegistEndtime(rs.getString("registEndtime"));
			p.setThumbUrl(rs.getString("thumbUrl"));
			p.setStartTime(rs.getString("startTime"));
			p.setEndTime(rs.getString("endTime"));
			p.setLongitude(rs.getDouble("longitude"));
			p.setLatitude(rs.getDouble("latitude"));
			p.setRadius(rs.getString("radius"));
			p.setTypes(rs.getInt("types"));
			p.setAddress(rs.getString("address"));
			p.setToWhom(rs.getString("toWhom"));
			p.setIntegral(rs.getDouble("integral"));
			p.setId(rs.getInt("id"));
			list.add(p);
		}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	return list;
	}
	public static ArrayList<SearchCredit> getSearchCredits(String keywords){
		ArrayList<SearchCredit> list=new ArrayList<SearchCredit>();
		String sql;
		sql="call a('"+keywords+"')";
		Connection con=getConnection();
		try {
			ResultSet rs = con.createStatement().executeQuery(sql);
			while(rs.next()){
			SearchCredit p=new SearchCredit();
			p.setXh(rs.getInt("xh"));
			p.setTitle(rs.getString("title"));
			p.setRegStartTime(rs.getString("regStartTime"));
			p.setRegEndTime(rs.getString("regEndTime"));
			p.setIntegral(rs.getDouble("integral"));
			list.add(p);
		}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	return list;
	}
	
	
public static ArrayList<GetClassStudents> getClassStudents(String grade,String year){
		ArrayList<GetClassStudents> list=new ArrayList<GetClassStudents>();
		String sql;
		sql="call sc('"+year+"','"+grade+"')";
		Connection con=getConnection();
		try {
			ResultSet rs = con.createStatement().executeQuery(sql);
			while(rs.next()){
			GetClassStudents p=new GetClassStudents();
			p.setXh(rs.getString("xh"));
			p.setXm(rs.getString("xm"));
			p.setTitle(rs.getString("title"));
			p.setMajor(rs.getString("major"));
			p.setIntegral(rs.getDouble("integral"));
			list.add(p);
		}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	return list;
	}
public static ArrayList<GetCollegeTable> getCollegeStudents(String dept,String year){
	ArrayList<GetCollegeTable> list=new ArrayList<GetCollegeTable>();
	String sql;
	sql="call sct('"+dept+"','"+year+"')";
	Connection con=getConnection();
	try {
		ResultSet rs = con.createStatement().executeQuery(sql);
		while(rs.next()){
		GetCollegeTable p=new GetCollegeTable();
		p.setXh(rs.getString("xh"));
		p.setXm(rs.getString("xm"));
		p.setMajor(rs.getString("major"));
		p.setDept(rs.getString("dept"));
		p.setIntegral(rs.getDouble("total"));
		list.add(p);
	}
		rs.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}
return list;
}


public static ArrayList<Charts> getCharts(String keywords,int typeid){
	ArrayList<Charts> list=new ArrayList<Charts>();
	String sql="";
	if(keywords==null || keywords==""){
		sql="SELECT audit.id,audit.xh,student.major,audit.activityname,audit.starttime,audit.xf,audit.description,audit.image FROM audit JOIN student on audit.xh = student.xh where (teachercheck+collegecheck+schoolcheck)='"+(typeid-1)+"' ORDER BY starttime asc";
	}else{
		sql="SELECT audit.id,audit.xh,student.major,audit.activityname,audit.starttime,audit.xf,audit.description,audit.image FROM audit JOIN student on audit.xh = student.xh where (audit.xh='"+keywords+"' or audit.activityname like '%"+keywords+"%') and (teachercheck+collegecheck+schoolcheck)='"+(typeid-1)+"' ORDER BY starttime asc";
	}
	
	Connection con=getConnection();
	try {
		ResultSet rs = con.createStatement().executeQuery(sql);
		while(rs.next()){
		Charts p=new Charts();
		p.setId(rs.getString("id"));
		p.setXh(rs.getString("xh"));
		p.setMajor(rs.getString("major"));
		p.setActivityname(rs.getString("activityname"));
		p.setStarttime(rs.getString("starttime"));
		p.setXf(rs.getString("xf"));
		p.setDescription(rs.getString("description"));
		p.setImage(rs.getString("image"));
		list.add(p);
	}
		rs.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return list;
}
public static boolean UpdateCharts(int uid,int tid){
	boolean r=true;
	String sql=null;
	Connection con=getConnection();
	if(uid==1){
		sql="update audit set teachercheck=1 where id=?";
	}
	else if(uid==2){
		sql="update audit set collegecheck=1 where id=?";
	}
	else{
		sql="update audit set schoolcheck=1 where id=?";
	}
	try {
		PreparedStatement pstm=con.prepareStatement(sql);
		pstm.setString(1,tid+"");
		r=pstm.execute();
	} catch (NumberFormatException e) {
		e.printStackTrace();
	} catch (SQLException e) {
		e.printStackTrace();
	}
	//返回false代表更新成功
	return r;
}
public static boolean DelCharts(int uid,int tid){
	boolean r=true;
	String sql=null;
	Connection con=getConnection();
	if(uid==0){
		return false;
	}else{
		sql="delete from audit where id=?";
	}
	try {
		PreparedStatement pstm=con.prepareStatement(sql);
		pstm.setString(1,tid+"");
		r=pstm.execute();
	} catch (NumberFormatException e) {
		e.printStackTrace();
	} catch (SQLException e) {
		e.printStackTrace();
	}
	//返回false代表删除成功
	return r;
}

}

jdbc数据库驱动的使用例子

public Admin checkadmin(){
	Admin r=null;
	try {
		Class.forName("com.mysql.jdbc.Driver");
		//连接
		String url="jdbc:mysql://localhost:3306/ygc";
		String user="root";
		String password="123456";
		Connection con=DriverManager.getConnection(url, user, password);
		//statement语句查
		Statement st=con.createStatement();;
		//返回结果集
		String sql="select * from admin where username='"+this.username+"' and password='"+this.password+"'";
		ResultSet rs=st.executeQuery(sql);
		//用(验证)
		if(rs.next()){
			this.realname=rs.getString("realname");
			this.img=rs.getString("img");
			this.phone=rs.getString("phone");
			this.email=rs.getString("email");
			this.id=rs.getInt("id");
			this.typeid=rs.getInt("typeid");
			r=this;
		  }
		//关闭
		rs.close();
		st.close();
		con.close();
	} catch (ClassNotFoundException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return r;
}