java旅行第三站--数据库第二天--简单的增删改查

public class JDBCMysqlPool {

	private Connection conn;
	private Statement stmt;
	private ResultSet rs;
	private int update;
	private PreparedStatement pstmt;
	private CallableStatement cst;

	// 加载mysql驱动
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public Connection getConnection() {
		Properties pro = new Properties();
		try {
			pro.load(new FileInputStream("freshbin/mysql.properties"));
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		String url = pro.getProperty("url");
		String user = pro.getProperty("user");
		String password = pro.getProperty("password");

		try {
			conn = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}

	public Statement getStatement() {
		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return stmt;
	}

	public ResultSet getResultSetQuery(String sql) {

		try {
			rs = stmt.executeQuery(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return rs;
	}
	
	public int getResultSetUpdate(String sql) {

		try {
			update = stmt.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return update;
	}

	public PreparedStatement getPreparedStatement(String sql) {
		
		try {
			pstmt = conn.prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return pstmt;
	}

	public ResultSet getPreResultSet() {

		try {
			rs = pstmt.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return rs;
	}
	
	public int getPreResultSetUpdate() {

		try {
			update = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return update;
	}
	
	public CallableStatement getCst(String sql) {
		try {
			cst = conn.prepareCall(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return cst;
	}
	
	public ResultSet getCallResultSetQuery() {

		try {
			rs = cst.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return rs;
	}
	
	public void close() {

		try {
			if(rs != null) {
				rs.close();
			}
			if(stmt != null) {
				stmt.close();
			}
			if(pstmt != null) {
				pstmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	
}

上面是连接数据库等操作，url和usename还有password存放在配置文件中

然后是statement的增删改查以及结果如下

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		JDBCMysqlPool jdbc = new JDBCMysqlPool();
		Connection conn = jdbc.getConnection();
		Statement stmt = jdbc.getStatement();
		ResultSet rs = jdbc.getResultSetQuery("select * from student");
		try {
			while(rs.next()) {
				System.out.println(rs.getString(1) + "--" + rs.getString(2) + "--" + rs.getString(3));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			jdbc.close();
		}

	}

	public static void main(String[] args) {
		JDBCMysqlPool jdbc = new JDBCMysqlPool();
		Connection conn = jdbc.getConnection();
		Statement stmt = jdbc.getStatement();
		System.out.println(jdbc.getResultSetUpdate("insert into student(name,hoby) value ('tyb', '读代码')"));
		jdbc.close();
	}

	public static void main(String[] args) {
		JDBCMysqlPool jdbc = new JDBCMysqlPool();
		Connection conn = jdbc.getConnection();
		Statement stmt = jdbc.getStatement();
		System.out.println(jdbc.getResultSetUpdate("update student set hoby='看代码' where hoby='读代码'"));
		jdbc.close();
	}

	public static void main(String[] args) {
		JDBCMysqlPool jdbc = new JDBCMysqlPool();
		Connection conn = jdbc.getConnection();
		Statement stmt = jdbc.getStatement();
		System.out.println(jdbc.getResultSetUpdate("delete from student where hoby='看代码'"));
		jdbc.close();
	}

然后是PreparedStatement的增删改查

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		JDBCMysqlPool jdbc = new JDBCMysqlPool();
		Connection conn = jdbc.getConnection();
		PreparedStatement pstmt = jdbc.getPreparedStatement("select * from student");
		ResultSet rs = jdbc.getPreResultSet();
		try {
			while(rs.next()) {
				System.out.println(rs.getInt("id") + "--" + rs.getString("name") + "--" + rs.getString("hoby"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			jdbc.close();
		}

	}

	public static void main(String[] args) {
		JDBCMysqlPool jdbc = new JDBCMysqlPool();
		Connection conn = jdbc.getConnection();
		PreparedStatement pstmt = jdbc.getPreparedStatement("insert into student(name,hoby) values(?,?)");
		try {
			pstmt.setString(1, "tyb");
			pstmt.setString(2, "读代码");
			System.out.println(jdbc.getPreResultSetUpdate());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			jdbc.close();
		}
	}

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		JDBCMysqlPool jdbc = new JDBCMysqlPool();
		Connection conn = jdbc.getConnection();
		PreparedStatement pstmt = jdbc.getPreparedStatement("update student set hoby='看代码' where hoby='读代码'");
		System.out.println(jdbc.getPreResultSetUpdate());
		jdbc.close();
	}

	public static void main(String[] args) {
		JDBCMysqlPool jdbc = new JDBCMysqlPool();
		Connection conn = jdbc.getConnection();
		PreparedStatement pstmt = jdbc.getPreparedStatement("delete from student where hoby='看代码'");
		System.out.println(jdbc.getPreResultSetUpdate());
		jdbc.close();
	}

下面是调用数据库的系统函数和自定义函数还有存储过程

public static void main(String[] args) {

		JDBCMysqlPool jdbc = new JDBCMysqlPool();
		Connection conn = jdbc.getConnection();
		CallableStatement cst = jdbc.getCst("select UPPER(?)");

		try {
			cst.setString(1, "freshbin");
			ResultSet rs = jdbc.getCallResultSetQuery();
			while(rs.next()) {
				System.out.println(rs.getString(1));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			jdbc.close();
		}
	}

public static void main(String[] args) {
		// TODO Auto-generated method stub
		JDBCMysqlPool jdbc = new JDBCMysqlPool();
		Connection conn = jdbc.getConnection();
		CallableStatement cst = jdbc.getCst("select freshbinf_add(?,?)");

		try {
			cst.setInt(1, 1);
			cst.setInt(2, 2);
			ResultSet rs = jdbc.getCallResultSetQuery();
			rs.next();
			System.out.println(rs.getString(1));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			jdbc.close();
		}

	}

public static void main(String[] args) {
		// TODO Auto-generated method stub
		JDBCMysqlPool jdbc = new JDBCMysqlPool();
		Connection conn = jdbc.getConnection();
		CallableStatement cst = jdbc.getCst("call getCountUsersNum(?,?)");

		try {
			cst.setString(1, "freshbin");
			cst.registerOutParameter(2, java.sql.Types.INTEGER);
			cst.execute();
			System.out.println(cst.getInt(2));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			jdbc.close();
		}
	}