dbconnection.java

通讯录 用java做的带图形界面的 一个简单通讯录 数据库有MYSQL支持 可以进行 查找 添加 删除 等

package tongxinglu;

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


public class DBConnection {
	//类变量，存放类实例化后的对象
	static private DBConnection instance = null;
	//数据库连接
	private Connection conn = null;
	//构造方法，加载驱动，取得数据库连接
	private DBConnection(){
		String driverClassName = "org.gjt.mm.mysql.Driver";
		String ur1 ="jdbc:mysql://localhost:3306/contacts";
		String user="root";
		String pwd="8170207";
		//加载驱动
		try {
			Class.forName(driverClassName).newInstance();
		} catch (Exception e) {
			// TODO 自动生成 catch 块
			System.out.println(e.getMessage());
		} 
		//连接数据库
		try {
			conn = DriverManager.getConnection(ur1,user,pwd);
			conn.setAutoCommit(true);
		} catch (SQLException sqlex) {
			// TODO 自动生成 catch 块
			sqlex.printStackTrace();
		}
	}
	
	
	//返回一个单独的实例，如果这个方法是第一次被调用就创建一个新的实例
	public static DBConnection getInstance(){
		if(instance==null){
			instance = new DBConnection();
		}
		return instance;
	}
	
	//将输入转化成所要的SQL语句，为后面调用
	//对于Contacts对象的每一个变量判断是否为空，处理见代码
	private String inputToSql(Contacts c){
		String sql = "";
		String temp = null;
		int cnt = 0;
		temp = c.getName();
		if(temp !=null&&temp.length()>0){
			cnt++;
			if(cnt>1){
				sql += "and name = '"+temp+"' ";
			}else{
				sql += "where name = '"+temp+"' ";
			}
		}
		temp = c.getBirthday();
		if(temp !=null&&temp.length()>0){
			cnt++;
			if(cnt>1){
				sql += "and birthday = '"+temp+"' ";
			}else{
				sql += "where birthday = '"+temp+"' ";
			}
		}
		temp = c.getEmail();
		if(temp !=null&&temp.length()>0){
			cnt++;
			if(cnt>1){
				sql += "and email = '"+temp+"' ";
			}else{
				sql += "where email = '"+temp+"' ";
			}
		}
		temp = c.getTel();
		if(temp !=null&&temp.length()>0){
			cnt++;
			if(cnt>1){
				sql += "and tel = '"+temp+"' ";
			}else{
				sql += "where tel = '"+temp+"' ";
			}
		}
		temp = c.getRemark();
		if(temp !=null&&temp.length()>0){
			cnt++;
			if(cnt>1){
				sql += "and remark = '"+temp+"' ";
			}else{
				sql += "where remark = '"+temp+"' ";
			}
		}
		return sql;
	}
	
	//插入数据到表Contacts中
	public int insert(Contacts c){
		int ans = 0;
		String sql = "insert into contacts(name,birthday,tel,email,remark) values" +
				"(?,?,?,?,?)";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, c.getName());
			pstmt.setString(2, c.getBirthday());
			pstmt.setString(3, c.getTel());
			pstmt.setString(4, c.getEmail());
			pstmt.setString(5, c.getRemark());
			ans = pstmt.executeUpdate();
			pstmt.close();
		} catch (SQLException sqlex) {
			// TODO 自动生成 catch 块
			sqlex.printStackTrace();
		}
		return ans;
	}
	
	//更新数据表Contacts,以name为关键字查询
	public int Updata(Contacts c){
		int ans = 0;
		String sql = "update contacts set birthday = '?'"+
			"tel = '?',email = '?',remark = '?' where name = '?'";
		
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, c.getBirthday());
			pstmt.setString(2, c.getTel());
			pstmt.setString(3, c.getEmail());
			pstmt.setString(4, c.getRemark());
			pstmt.setString(5, c.getName());
			ans = pstmt.executeUpdate();
			pstmt.close();
		} catch (SQLException sqlex) {
			// TODO 自动生成 catch 块
			sqlex.printStackTrace();
		}
		//如果数据表中不存在这样的数据则插入这组数据
		if(ans<=0){
			ans = insert(c);
		}
		return ans;
	}
	
	//查询数据表，以Contacts为条件，如果这个对象的某个变量为空，
	//则不作为查询条件，将查询结果存在list对象中返回
	public List<Contacts>Query(Contacts c){
		List<Contacts> list = new ArrayList<Contacts>();
		String sql = "select * from contacts ";
		sql += inputToSql(c);
		System.out.println(sql);
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				//遍历所有数据
				//取得一条数据
				Contacts tempc = new Contacts();
				tempc.setName(rs.getString("name"));
				tempc.setBirthday(rs.getString("birthday"));
				tempc.setTel(rs.getString("tel"));
				tempc.setEmail(rs.getString("email"));
				tempc.setRemark(rs.getString("remark"));
				//添加到List
				list.add(tempc);
			}	
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		return list;
		
	}
	//删除表中数据，如果这个对象的某个变量为空，
	//则不作为查询条件，返回影响的行数
	public int Delete(Contacts c){
		List<Contacts> list = new ArrayList<Contacts>();
		String sql = "delete from contacts ";
		int ans = 0;
		sql +=inputToSql(c);
		System.out.println(sql);
		try {
			Statement stmt = conn.createStatement();
			ans = stmt.executeUpdate(sql);
			stmt.close();
		} catch (SQLException sqlex) {
			// TODO 自动生成 catch 块
			sqlex.printStackTrace();
		}
		return ans;
	}
	
	//关闭数据库
	public void close(){
		try {
			conn.close();
		} catch (SQLException sqlex) {
			// TODO 自动生成 catch 块
			sqlex.printStackTrace();
		}
	}
}