jdbcconn.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 JDBCConn {
	private String url = "jdbc:mysql://localhost:3306/test";

	private String user = "root";

	private String pwd = "";

	private Connection conn = null;

	public JDBCConn() {// 构造方法加载驱动连接数据库
		try {
			String driverName = null;
			driverName = "org.gjt.mm.mysql.Driver";
			Class.forName(driverName);
			conn = DriverManager.getConnection(url, user, pwd);
		} catch (ClassNotFoundException e) {
		} catch (SQLException se) {
			System.out.println(" " + se.getMessage());
		}
	}

	// 按指定的url,user,pwd加载驱动，连接数据库
	public JDBCConn(String url, String user, String pwd) {// 重载
		this.url = url;
		this.user = user;
		this.pwd = pwd;
		try {
			String driverName = null;
			driverName = "org.gjt.mm.mysql.Driver";
			Class.forName(driverName);
			conn = DriverManager.getConnection(url, user, pwd);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException se) {
			se.printStackTrace();
		}
	}

	// 当没有用户名和密码
	public JDBCConn(String url) {// 重载
		this.url = url;
		try {
			String driverName = null;
			driverName = "org.gjt.mm.mysql.Driver";
			Class.forName(driverName);
			conn = DriverManager.getConnection(url);

		} catch (ClassNotFoundException e) {
		} catch (SQLException se) {
			System.out.println(" " + se.getMessage());
		}
	}

	// searchByStatement方法用于查询数据库，返回值为List类型
	// 所查询的为指定的不带参数的SQL语句
	public List<User> searchByStatement(String sql) {
		ResultSet rs = null;
		String temp = "select * from userinfo";
		List<User> list = new ArrayList<User>();
		// 当sql为空时，使用默认参数
		if (sql != null) {
			temp = sql;
		}
		try {
			Statement stmt = conn.createStatement();
			rs = stmt.executeQuery(temp);
			while (rs.next()) {
				User u = new User();
				u.setId(rs.getInt("id"));
				u.setUsername(rs.getString("username"));
				u.setAge(rs.getInt("age"));
				u.setPassword(rs.getString("password"));
				list.add(u);
			}
			rs.close();
			rs = null;
			stmt.close();
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
		return list;
	}

	// 本方法为查询指定的带参数的SQL语句
	// 返回年龄等于age的所有人的资料，返回类型为List。
	public List<User> searchByPS(int age) {
		ResultSet rs = null;
		List<User> list = new ArrayList<User>();
		String sql = "select * from userinfo where age = ?";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, age);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				// 遍历整个数据集，取出数据存入List中
				User u = new User();
				u.setId(rs.getInt("id"));
				u.setUsername(rs.getString("username"));
				u.setAge(rs.getInt("age"));
				u.setPassword(rs.getString("password"));
				list.add(u);
			}
			rs.close();
			rs = null;
			pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	// Insert方法用于添加一组数据
	public void Insert(User u) {
		String sql = "insert into userinfo(id, username, password, age) values(?,?,?,?)";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, u.getId());
			pstmt.setString(2, u.getUsername());
			pstmt.setString(3, u.getPassword());
			pstmt.setInt(4, u.getAge());
			pstmt.execute();
			pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	// Update方法用于修改数据库返回int型数据，表示影响到的行数
	public int Update(User u) {
		int cnt= 0;
		String sql = "update userinfo set username=?, password=?, age=? where id = ?";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, u.getUsername());
			pstmt.setString(2, u.getPassword());
			pstmt.setInt(3, u.getAge());
			pstmt.setInt(4, u.getId());
			cnt = pstmt.executeUpdate();
			pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return cnt;
	}
	// 删除id为所给id的数据
	public void Delete(int id) {
		String sql = "delete from userinfo where id = ?";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			// 将指定参数设置为给定 Java int 值。
			pstmt.setInt(1, id);
			// 执行SQL语句
			pstmt.executeUpdate();
			// 关闭对象
			pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	// Close关闭数据库连接
	public void Close() {
		try {
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}