JDBC PreparedStatement Example – Select Data List From Table

To select data from table use java.sql.PreparedSatement, you need first create a select sql command, and then get data list by execute java.sql.PreparedSatement.executeQuery() method. This article will show you examples.

This article will use MySQL server and teacher table.

Code Snippet

PreparedStatement pStmt = dbConn.prepareStatement(sqlBuf.toString());
				
pStmt.setString(1, "jerry");
				
ResultSet rs = pStmt.executeQuery();
				
while(rs.next())
{
   ......
}

Full Example Code

	/* Query teacher table use PreparedStatement and return DTO list. */
	public List selectTeacherDtoListUsePreparedStatement(String ip, int port, String dbName, String userName, String password)
	{
		List ret = new ArrayList();
		
		Connection dbConn = null;
		PreparedStatement pStmt = null;
		try
		{
			dbConn = this.getMySqlConnection(ip, port, dbName, userName, password);
			
			if(dbConn!=null)
			{
				StringBuffer sqlBuf = new StringBuffer();
				
				sqlBuf.append("select id, name, email, registTime from `teacher` where name = ?");
				
				pStmt = dbConn.prepareStatement(sqlBuf.toString());
				
				pStmt.setString(1, "jerry");
				
				ResultSet rs = pStmt.executeQuery();
				
				while(rs.next())
				{
					TeacherDTO tDto = new TeacherDTO();
					
					int tId = rs.getInt(1);
					tDto.setId(tId);
					
					String tName = rs.getString(2);
					tDto.setName(tName);
					
					String tEmail = rs.getString(3);
					tDto.setEmail(tEmail);

					Timestamp tRegistTime = rs.getTimestamp(4);					
					tDto.setRegistTime(tRegistTime);

					ret.add(tDto);
					
					System.out.println("Teacher id : " + tDto.getId() + " , name : " + tDto.getName() + " , email : " + tDto.getEmail() + " , registTime : " + tDto.getRegistTime());
				}
				
				System.out.println("query record in table success. ");
			}
		}catch(Exception ex)
		{
			ex.printStackTrace();
		}finally
		{
			this.closeDBResource(pStmt, dbConn);
		}
		
		return ret;
	}

	/* This method return java.sql.Connection object from MySQL server. */
	public Connection getMySqlConnection(String ip, int port, String dbName, String userName, String password)
	{
		/* Declare and initialize a sql Connection variable. */
		Connection ret = null;
		
		try
		{
		
			/* Register for mysql jdbc driver class. */
			Class.forName("com.mysql.jdbc.Driver");
			
			/* Create mysql connection url. */
			String mysqlConnUrl = "jdbc:mysql://" + ip + ":" + port + "/" + dbName;
			
			/* Get the mysql Connection object. */
			ret = DriverManager.getConnection(mysqlConnUrl, userName , password);
		}catch(Exception ex)
		{
			ex.printStackTrace();
		}finally
		{
			return ret;
		}
	}
	
	/* Close prepared statement and connection after use, this can avoid resource waste. */
	public void closeDBResource(PreparedStatement pStmt, Connection conn)
	{
		try
		{
			if(pStmt!=null)
			{
				pStmt.close();
				pStmt = null;
			}
			
			if(conn!=null)
			{
				conn.close();
				conn = null;
			}
		}catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}

TeacherDTO.java

/* This class represent one record in database teacher table. */
public class TeacherDTO {
	
	private int id;
	
	private String name;
	
	private String email;
	
	private Timestamp registTime;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public Timestamp getRegistTime() {
		return registTime;
	}

	public void setRegistTime(Timestamp registTime) {
		this.registTime = registTime;
	}
}

Source Code

1. [download id=”2604″]