Select query database java

SQLite Java: Select Data

Summary: in this tutorial, you will learn how to query data from a table in the SQLite database using Java JDBC.

To query data from a table, you use the following steps:

1. First, create a Connection object to connect to the SQLite database.
2. Next, create an instance of the Statement class from the Connection object.
3. Then, create an instance of the ResultSet class by calling the executeQuery method of the Statement object. The executeQuery() method accepts a SELECT statement.
4. After that, loop through the result set using the next() method of the ResultSet object.
5. Finally, use the get* method of the ResultSet object such as getInt() , getString() , getDouble() , etc., to get the data in each iteration.

The following program selects all rows from the warehouses table.

package net.sqlitetutorial; import java.sql.DriverManager; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * * @author sqlitetutorial.net */ public class SelectApp < /** * Connect to the test.db database * @return the Connection object */ private Connection connect() < // SQLite connection string String url = "jdbc:sqlite:C://sqlite/db/test.db"; Connection conn = null; try < conn = DriverManager.getConnection(url); >catch (SQLException e) < System.out.println(e.getMessage()); >return conn; > /** * select all rows in the warehouses table */ public void selectAll()< String sql = "SELECT id, name, capacity FROM warehouses"; try (Connection conn = this.connect(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql))< // loop through the result set while (rs.next()) < System.out.println(rs.getInt("id") + "\t" + rs.getString("name") + "\t" + rs.getDouble("capacity")); > > catch (SQLException e) < System.out.println(e.getMessage()); >> /** * @param args the command line arguments */ public static void main(String[] args) < SelectApp app = new SelectApp(); app.selectAll(); > >Code language: Java (java)

The following illustrates the output of the program:

Querying data with parameters

To use parameters in the query, you use the PreparedStatement object instead. For example, the following method selects the warehouse whose capacity is greater than a specified capacity.

 /** * Get the warehouse whose capacity greater than a specified capacity * @param capacity */ public void getCapacityGreaterThan(double capacity)< String sql = "SELECT id, name, capacity " + "FROM warehouses WHERE capacity > ?"; try (Connection conn = this.connect(); PreparedStatement pstmt = conn.prepareStatement(sql))< // set the value pstmt.setDouble(1,capacity); // ResultSet rs = pstmt.executeQuery(); // loop through the result set while (rs.next()) < System.out.println(rs.getInt("id") + "\t" + rs.getString("name") + "\t" + rs.getDouble("capacity")); > > catch (SQLException e) < System.out.println(e.getMessage()); >>Code language: Java (java)

To find the warehouses whose capacities are greater than 3600, you use the getCapacityGreaterThan() method as follows:

SelectApp app = new SelectApp(); app.getCapacityGreaterThan(3600);Code language: Java (java)

The following is the output:

In this tutorial, you have learned how to query data from the table in the SQLite database from a Java program.

Источник

Processing SQL Statements with JDBC

In general, to process any SQL statement with JDBC, you follow these steps:

This page uses the following method, CoffeesTable.viewTable , from the tutorial sample to demonstrate these steps. This method outputs the contents of the table COFFEES . This method will be discussed in more detail later in this tutorial:

public static void viewTable(Connection con) throws SQLException < String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES"; try (Statement stmt = con.createStatement()) < ResultSet rs = stmt.executeQuery(query); while (rs.next()) < String coffeeName = rs.getString("COF_NAME"); int supplierID = rs.getInt("SUP_ID"); float price = rs.getFloat("PRICE"); int sales = rs.getInt("SALES"); int total = rs.getInt("TOTAL"); System.out.println(coffeeName + ", " + supplierID + ", " + price + ", " + sales + ", " + total); >> catch (SQLException e) < JDBCTutorialUtilities.printSQLException(e); >>

Establishing Connections

First, establish a connection with the data source you want to use. A data source can be a DBMS, a legacy file system, or some other source of data with a corresponding JDBC driver. This connection is represented by a Connection object. See Establishing a Connection for more information.

Creating Statements

A Statement is an interface that represents a SQL statement. You execute Statement objects, and they generate ResultSet objects, which is a table of data representing a database result set. You need a Connection object to create a Statement object.

For example, CoffeesTable.viewTable creates a Statement object with the following code:

There are three different kinds of statements:

• Statement : Used to implement simple SQL statements with no parameters.
• PreparedStatement : (Extends Statement .) Used for precompiling SQL statements that might contain input parameters. See Using Prepared Statements for more information.
• CallableStatement: (Extends PreparedStatement .) Used to execute stored procedures that may contain both input and output parameters. See Stored Procedures for more information.

Executing Queries

To execute a query, call an execute method from Statement such as the following:

• execute : Returns true if the first object that the query returns is a ResultSet object. Use this method if the query could return one or more ResultSet objects. Retrieve the ResultSet objects returned from the query by repeatedly calling Statement.getResultSet .
• executeQuery : Returns one ResultSet object.
• executeUpdate : Returns an integer representing the number of rows affected by the SQL statement. Use this method if you are using INSERT , DELETE , or UPDATE SQL statements.

For example, CoffeesTable.viewTable executed a Statement object with the following code:

ResultSet rs = stmt.executeQuery(query);

Processing ResultSet Objects

You access the data in a ResultSet object through a cursor. Note that this cursor is not a database cursor. This cursor is a pointer that points to one row of data in the ResultSet object. Initially, the cursor is positioned before the first row. You call various methods defined in the ResultSet object to move the cursor.

For example, CoffeesTable.viewTable repeatedly calls the method ResultSet.next to move the cursor forward by one row. Every time it calls next , the method outputs the data in the row where the cursor is currently positioned:

ResultSet rs = stmt.executeQuery(query); while (rs.next()) < String coffeeName = rs.getString("COF_NAME"); int supplierID = rs.getInt("SUP_ID"); float price = rs.getFloat("PRICE"); int sales = rs.getInt("SALES"); int total = rs.getInt("TOTAL"); System.out.println(coffeeName + ", " + supplierID + ", " + price + ", " + sales + ", " + total); >// .

Closing Connections

When you are finished using a Connection , Statement , or ResultSet object, call its close method to immediately release the resources it’s using.

Alternatively, use a try -with-resources statement to automatically close Connection , Statement , and ResultSet objects, regardless of whether an SQLException has been thrown. (JDBC throws an SQLException when it encounters an error during an interaction with a data source. See Handling SQL Exceptions for more information.) An automatic resource statement consists of a try statement and one or more declared resources. For example, the CoffeesTable.viewTable method automatically closes its Statement object, as follows:

public static void viewTable(Connection con) throws SQLException < String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES"; try (Statement stmt = con.createStatement()) < ResultSet rs = stmt.executeQuery(query); while (rs.next()) < String coffeeName = rs.getString("COF_NAME"); int supplierID = rs.getInt("SUP_ID"); float price = rs.getFloat("PRICE"); int sales = rs.getInt("SALES"); int total = rs.getInt("TOTAL"); System.out.println(coffeeName + ", " + supplierID + ", " + price + ", " + sales + ", " + total); >> catch (SQLException e) < JDBCTutorialUtilities.printSQLException(e); >>

The following statement is a try -with-resources statement, which declares one resource, stmt , that will be automatically closed when the try block terminates:

try (Statement stmt = con.createStatement()) < // . >

Источник

A Java MySQL SELECT example

Summary: This is a Java/MySQL SQL SELECT example, demonstrating how to issue a SQL SELECT command from your Java source code, using a MySQL database.

I’ve written a short example program that shows how to perform a SELECT query against a MySQL database in Java. Let’s take a look at it.

An example MySQL database table

The first thing we need for our SQL SELECT query example is a sample database table. To keep it simple — but also show several different MySQL data types — I’ve created the following example database table:

create table users ( id int unsigned auto_increment not null, first_name varchar(32) not null, last_name varchar(32) not null, date_created timestamp default now(), is_admin boolean, num_points int, primary key (id) );

A few of these MySQL fields are a little contrived, but I wanted to show several different data types in one table, and this is what I came up with. In particular, the field num_points is a little unusual. I made it up so I could show an int data type in this table, and I was thinking of those websites where points are awarded for giving correct answers.

I’ve populated this database table in some related articles (Java MySQL INSERT using Statement, Java MySQL INSERT using PreparedStatement), so when I run this SELECT query from the MySQL command prompt:

+----+------------+-----------+---------------------+----------+------------+ | id | first_name | last_name | date_created | is_admin | num_points | +----+------------+-----------+---------------------+----------+------------+ | 2 | Fred | Flinstone | 2010-06-23 00:00:00 | 0 | 6000 | | 3 | Barney | Rubble | 2010-06-23 00:00:00 | 0 | 5000 | +----+------------+-----------+---------------------+----------+------------+ 2 rows in set (0.00 sec)

With a little example data in my database, I’m ready to write some Java/JDBC code.

The Java source code

To perform a SQL SELECT query from Java, you just need to follow these steps:

1. Create a Java Connection to the MySQL database
2. Define the SELECT statement
3. Execute the SELECT query, getting a Java ResultSet from that query
4. Iterate over the ResultSet , getting the database fields (columns) from each row of data that is returned
5. Close the Java database connection
6. Catch any SQL exceptions that may come up during the process

I tried to document the following Java/MySQL SELECT example so you can see these steps. Note that in this example my MySQL database username is “root”, my password is blank, and the database is running on the same computer where this program is run, so the database host name is “localhost”:

import java.sql.*; /** * A Java MySQL SELECT statement example. * Demonstrates the use of a SQL SELECT statement against a * MySQL database, called from a Java program. * * Created by Alvin Alexander, http://alvinalexander.com */ public class JavaMysqlSelectExample < public static void main(String[] args) < try < // create our mysql database connection String myDriver = "org.gjt.mm.mysql.Driver"; String myUrl = "jdbc:mysql://localhost/test"; Class.forName(myDriver); Connection conn = DriverManager.getConnection(myUrl, "root", ""); // our SQL SELECT query. // if you only need a few columns, specify them by name instead of using "*" String query = "SELECT * FROM users"; // create the java statement Statement st = conn.createStatement(); // execute the query, and get a java resultset ResultSet rs = st.executeQuery(query); // iterate through the java resultset while (rs.next()) < int String firstName = rs.getString("first_name"); String lastName = rs.getString("last_name"); Date dateCreated = rs.getDate("date_created"); boolean isAdmin = rs.getBoolean("is_admin"); int numPoints = rs.getInt("num_points"); // print the results System.out.format("%s, %s, %s, %s, %s, %s\n", id, firstName, lastName, dateCreated, isAdmin, numPoints); >st.close(); > catch (Exception e) < System.err.println("Got an exception! "); System.err.println(e.getMessage()); >> >

The results

Assuming that everything is set up properly on your computer system, you should see output like this when you run this example Java program:

2, Fred, Flinstone, 2010-06-23, false, 6000 3, Barney, Rubble, 2010-06-23, false, 5000

Of course your output will vary depending on the actual data in your database table.

Regarding your setup, the main things you’ll need are a Java compiler (SDK), the MySQL JDBC database driver, and a MySQL instance running on your computer (or running on another computer you can access).

In “real world” Java database programs I almost always use the Spring JDBC library to access a database, but when you’re first getting started, I think it’s important to see examples like this so you can understand how things work under the covers.

Summary: My Java MySQL SELECT example

As a quick recap, this example demonstrated the following steps:

1. How to create a Java Connection to a MySQL database
2. How to create a SQL SELECT statement
3. How to execute a Java MySQL SELECT query, getting a Java ResultSet from that query
4. How to iterate over the Java ResultSet , getting the database fields (columns) from each row of data that is returned
5. How to close a Java database connection
6. How to catch any SQL exceptions that may come up during the process

In summary, I hope this Java MySQL SELECT example is helpful.

Источник

mysql select query in java

In your statement » . q_table.QID='» + number_input + «‘ AND . the variable number_input is enclosed in a single quote (‘). This is used for string lieterals. If you remove the single quote it should work:

 String prest= "SELECT * FROM q_table, choices, answers WHERE questions.QID=? AND choices.CID=? AND answers.AID=?"; prest.setInt(1,1980); prest.setInt(2,2004); . . ResultSet rs = prest.executeQuery(); while (rs.next()) < String mov_name = rs.getString(1); int mov_year = rs.getInt(2); count++; System.out.println(mov_name + "\t" + "- " + mov_year); >System.out.println("Number of records: " + count); prest.close(); con.close(); 

Well, the first problem is that you’re opening yourself up to a SQL injection attack by including values directly in your SQL. Use a parameterized query instead.

Now we can’t really tell what’s wrong beyond that, although the fact that you’re quoting a number seems suspicious, as does the fact that you’re using the same value for a question ID, a choice ID and an answer ID. That seems unlikely to be appropriate.

If you could give us more information about what’s happening vs what you expected to happen, that would really help.

When you use prepared statements, you can’t set the values there.

You have to first prepare the statement using question marks and then set the parameters later.

public void updateCoffeeSales(HashMap salesForWeek) throws SQLException < PreparedStatement updateSales = null; PreparedStatement updateTotal = null; String updateString = "update " + dbName + ".COFFEES " + "set SALES = ? where COF_NAME = ?"; String updateStatement = "update " + dbName + ".COFFEES " + "set TOTAL = TOTAL + ? where COF_NAME = ?"; try < con.setAutoCommit(false); updateSales = con.prepareStatement(updateString); updateTotal = con.prepareStatement(updateStatement); for (Map.Entrye : salesForWeek.entrySet()) < updateSales.setInt(1, e.getValue().intValue()); updateSales.setString(2, e.getKey()); updateSales.executeUpdate(); updateTotal.setInt(1, e.getValue().intValue()); updateTotal.setString(2, e.getKey()); updateTotal.executeUpdate(); con.commit(); >> catch (SQLException e ) < JDBCTutorialUtilities.printSQLException(e); if (con != null) < try < System.err.print("Transaction is being rolled back"); con.rollback(); >catch(SQLException excep) < JDBCTutorialUtilities.printSQLException(excep); >> > finally < updateSales.close(); updateTotal.close(); con.setAutoCommit(true); >> 

Источник