JDBC PreparedStatement – Select list of rows
A JDBC PreparedStatement
example to select a list of rows from the database.
RowSelect.java
package com.mkyong.jdbc.preparestatement.row;
import com.mkyong.jdbc.model.Employee;
import java.math.BigDecimal;
import java.sql.*;
public class RowSelect {
private static final String SQL_SELECT = "SELECT * FROM EMPLOYEE";
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(
"jdbc:postgresql://127.0.0.1:5432/test", "postgres", "password");
PreparedStatement preparedStatement = conn.prepareStatement(SQL_SELECT)) {
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
long id = resultSet.getLong("ID");
String name = resultSet.getString("NAME");
BigDecimal salary = resultSet.getBigDecimal("SALARY");
Timestamp createdDate = resultSet.getTimestamp("CREATED_DATE");
Employee obj = new Employee();
obj.setId(id);
obj.setName(name);
obj.setSalary(salary);
// Timestamp -> LocalDateTime
obj.setCreatedDate(createdDate.toLocalDateTime());
System.out.println(obj);
}
} catch (SQLException e) {
System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
} catch (Exception e) {
e.printStackTrace();
}
}
}
Employee.java
package com.mkyong.jdbc.model;
import java.math.BigDecimal;
import java.time.LocalDateTime;
public class Employee {
private Long id;
private String name;
private BigDecimal salary;
private LocalDateTime createdDate;
//...
}
Table definition.
CREATE TABLE EMPLOYEE
(
ID serial,
NAME varchar(100) NOT NULL,
SALARY numeric(15, 2) NOT NULL,
CREATED_DATE timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
PRIMARY KEY (ID)
);
P.S Tested with PostgreSQL 11 and Java 8
pom.xml
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.5</version>
</dependency>
Download Source Code
$ git clone https://github.com/mkyong/java-jdbc.git
I think, in short example, the below line of code gives error:
ResultSet rs = preparedStatement.executeQuery(selectSQL );
Here we don’t need to pass “selectSQL” as parameter to executeQuery.
This error can be removed in following way:
ResultSet rs = preparedStatement.executeQuery();
Please correct me if I am wrong.
you’re right, it happened to me this same morning by mistake 😛
Esto es un foro de programación, no un lugar para mostrar ideologías políticas con banderas no-oficiales, Beri. ¿O es que acaso programas también en Catalán? Apuesto a que Arturo ya os ha lavado el cerebro diciendo que es tan importante el Catalán (primera lengua conocida de la humanidad) que con el paso de unos meses se impondrá hasta como lenguaje de programación. Leía hoy en el periódico que ha ido a EEUU y a dar una charla a la Universidad de Columbia y tras la misma va y afirma que allí están a favor del “referendum”… En serio, ¿no tenéis ninguno en esa comunidad autónoma el más mínimo sentido del ridículo?
Meas fuera del tiesto naZionalista de pacotilla.
Disculpa bonico pero, eres tú el que postea asuntos no
relacionados con la programación.
Por otro lado se ve de lejos que no conoces a muchos catalanes, ya que das de hecho que todos somos Arturistas, cuándo este señor es un mamarracho capitalista como cualquier otro de los que tenéis del Ebro para abajo.
Y lo del avatar, es el que uso en Disqus. Y como veo que hay gente a quien le escuece la vista tansolo de verlo, pues como que se va a quedar unos añitos ahí puesto 😛
No m’enterao de ná, pero te tengo como rojo y como tal te trato.
Saludos.
jajaja da gusto tener camaradas como tu de follower! Un saludo!!
yes… I think you are right. when I used that way, I got the exception java.sql.SQLException: Use of the executeQuery(String) method is not supported on this type of statement. but in the example, coding is right
pls is there any help i have this code put my ResultSet which i call it rst is not work why ?
prepareStatement pst =null;
ResultSet rst=null;
Connection con=null;
this is my code
String sql = “SELECT * FROM user WHERE username007 = ‘” + txtusername007.getText() + “‘”
+ ” AND password = ‘” + txtpassword.getText() + “‘ AND specialization ”
+ ” = ‘”+cspcilization.getSelectedItem() + “‘ “;
try {
pst = con.prepareStatement(sql);
rst= pst.executeQuery();
if( rst.next()) {
if ( cspcilization.getSelectedItem().equals(“Admin”)){
this.setVisible(false);
home h =new home();
h.setVisible(true);
h.m2.setEnabled(false);
h.m3.setEnabled(false);
h.m4.setEnabled(false);
h.m5.setEnabled(false);
h.m6.setEnabled(false);
h.m7.setEnabled(false);
h.m8.setEnabled(false);
}
I have the same problem..
just run executeQuery() and no executeQuery(String) when you are doing a SELECT query with params.
i hav excute following code but it throw exception that executeQuery() cannot take arguments on a PreparedStatement
try{
String insertSQL=”select txtPassword from new where txtUserName=’?'”;
PreparedStatement preparedStatement=connection.prepareStatement(insertSQL);
rs=preparedStatement.executeQuery(insertSQL);
System.out.print(rs);
}
catch(Exception e1)
{
System.out.print(e1.getMessage());
}
rs=preparedStatement.executeQuery();
This is the correct syntax
Create a java program that inserts numbers in table3 (numeral) starting with 100 and ending with 300 and also randomly inserts letters into table3(letter).
Use a prepared statement for the insert.
But what if we want to set a string? PreparedStatement.setString(1, Variable)?
I’m having this issue, the problem is that MySQL takes the query with the variable around apostrophes. I mean: “SELECT * FROM Table Where TableVarChar = ‘VariableValue'”…
MySQL won’t accept those apostrophes. Anyone else having this issue? Hope it helps someone in the future, as well to me 😀
I think MySQL does NOT accept the normal single quotes [i.e. ‘ ] but accepts the left tilted quotes which are found to the left of Number 1 on keyboard (or below the “ESC” button) [ i.e. ` ]
In the example shown above don’t we need to close resultSet in finally block.
Your executive summary incorrectly uses executeQuery passing the SQL in again, whereas the long version correctly uses executeQuery with no parameters.
– Appreciate this service!
Pls send me code for read data from excel sheet from a folder, after words inserted into specified table. send me full project code using jdbc
waiting reply
Seriously ?! Maybe you need your car washed, too ? 🙂
@mkyong thanks for this tutorial
For excel file, use Apache POI – http://poi.apache.org/
For JDBC, refer to this – https://mkyong.com/tutorials/jdbc-tutorials/