dbconnection.java

Java程序设计实用案例教程源代码

package database;

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 url = "jdbc:mysql://localhost:3306/contacts";
		String user = "root";
		String pwd = "123";
		// 加载驱动
		try {
			Class.forName(driverClassName).newInstance();
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
		// 连接数据库
		try {
			conn = DriverManager.getConnection(url, user, pwd);
			conn.setAutoCommit(true);
		} catch (SQLException sqlex) {
			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) {
			sqlex.printStackTrace();
		}
		return ans;
	}
	// 更新数据表Contacts，以name为关键字查询
	public int Update(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) {
				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 sqlex) {
			sqlex.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) {
			sqlex.printStackTrace();
		}
		return ans;
	}
	// 关闭数据库
	public void close() {
		try {
			conn.close();
		}catch(SQLException sqlex) {
			sqlex.printStackTrace();
		}
	}
}