JDBC 分页显示与自增长主键

一、表

create table client
(
  id int(3) primary key auto_increment,
  name varchar(20),
  password varchar(20)
);

二、JDBC工具类

package com.jdbc.demo;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * JDBC工具类，用来获得连接，关闭相关资源
 * @author jiazhengfeng
 *
 */
public class JDBCUtils {
	private static Connection con = null;
	private static String driver = null;
	private static String url = null;
	private static String username = null;
	private static String password = null;

	static {

		InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");

		try {

			Properties pro = new Properties();
			pro.load(in);
			driver = pro.getProperty("driver");
			url = pro.getProperty("url");
			username = pro.getProperty("username");
			password = pro.getProperty("password");

			Class.forName(driver);
			con = DriverManager.getConnection(url, username, password);

		} catch (IOException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static Connection getConnection() {
		return con;
	}

	public static void release(Connection con, Statement st, ResultSet rs) {

		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		if (st != null) {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		if (con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

三、数据库操作类

package com.jdbc.demo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ListClient {
	private static Connection con = null;
	private static PreparedStatement st = null;
	private static ResultSet rs = null;

	public static void main(String[] args) {
		
		//getGeneratedKeys();
		int start=0;
		int count=3;
		list(start,count);
		
		
}
	/**
	 * 分页显示
	 * @param start
	 * @param count
	 */
	public static void list(int start, int count) {
		
		try {
			con = JDBCUtils.getConnection();
			Statement st = con.createStatement();
			String sql = "select *from client limit "+start+","+count;
			ResultSet rs = st.executeQuery(sql);
			
			while(rs.next()){
				int id=rs.getInt("id");
				String name=rs.getString("name");
				String password=rs.getString("password");
				System.out.printf("%d\t%s\t%s\t\n",id,name,password);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.release(con, st, rs);
		}
	}
	/**
	 * 得到自增长主键
	 */
	public static void getGeneratedKeys() {
		con = JDBCUtils.getConnection();
		try {
			con = JDBCUtils.getConnection();
			String sql = "insert into client(name,password) values(?,?);";
			PreparedStatement st = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
			st.setString(1, "xiaofang");
			st.setString(2, "123456");

			st.execute();
			ResultSet rs = st.getGeneratedKeys();
			while (rs.next()) {
				System.out.println(rs.getInt(1));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.release(con, st, rs);
		}
	}