What is statement in java jdbc

Java JDBC SQL Statement Interface

A Statement is an interface that represents a Java SQL statement. When you execute Statement objects, then 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.

The execute method of java.sql.Statement executes an SQL statement and indicates the form of the first result. You must then use the methods getResultSet or getUpdateCount to retrieve the result, and getMoreResults to move to any subsequent result(s).

What is Java JDBC Statement ?

The Java JDBC Statement is a java.sql.Statement interface that sending insert statements, select statements, update statements and delete statements to databases using JDBC connection.

What are the Advantages of java.sql.Statement ?

1. Send queries and update statements to the database.
2. Insert records in to table.
3. Select ( Retrieve ) records from table.
4. Update records in a table.
5. Delete records from table.
6. Create database.
7. Create and alter tables in database.
8. Create and alter views in a database.
9. Create indexes on tables.

What are the Disadvantages of java.sql.Statement ?

1. You can not change query statement dynamically.
2. You can not pass parameters dynamically.
3. It can not prevent SQL injection ( SQLi ) attacks.

What is SQL Injection ( SQLi ) ?

SQL Injection in short SQLi is a SQL code ( either good or bad code ) injects through web parameter value into SQL statements, that allows an attacker to interfere with the queries of a database. These attacks either steal the information from database or damage the database with bad SQL code. So java.sql.Statement is not recommended to use with parameters as it won’t prevent SQL injection ( SQLi ) attacks.

How to Create SQL Statements using JDBC ?

To process any SQL statement with JDBC you should follow the following steps.

1. Create database connection.
2. Create SQL Statement.
3. Execute the Query.
4. Process the ResultSet object.
5. Close the ResultSet.
6. Close the SQL Statement.
7. Finally close the database connection.

Following java.sql.Statement example shows the above steps.

How to retrieve records from MySQL database using Java Example ?

import java.sql.DriverManager; import java.sql.Connection; import java.sql.Statement; import java.sql.ResultSet; public class MySQLJavaSQLStatement < public static void main(String args[]) < Connection sql_connection = null; Statement statement = null; ResultSet resultset = null; String database_user = "your database user"; String database_password = "your database password"; try < Class.forName("com.mysql.jdbc.Driver"); sql_connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/your_database_name", database_user, database_password); statement = sql_connection.createStatement(); resultset = statement.executeQuery("SELECT * FROM your_table"); System.out.println("Column1\tColumn2"); System.out.println("-------------------------"); while(resultset.next()) < System.out.println(resultset.getString(1) + "\t" + resultset.getString(2)); >> catch(Exception e) < System.out.println("Error Occured : " + e.getMessage()); >finally < try < if (resultset != null) < resultset.close(); resultset = null; >if (statement != null) < statement.close(); statement = null; >if (sql_connection != null) < if (!sql_connection.isClosed()) < sql_connection.close(); >sql_connection = null; > > catch(Exception ex) < System.out.println("MySQL Database Connection Close Error"); >> > >

More Related Tutorials

© 2010 — 2023 HudaTutorials.com All Rights Reserved.

Источник

Java JDBC – Statement Object to Execute Insert, Update and Delete

Statement Object is used for executing sql query like Insert , Update , Delete and Select in the database. It provides methods to execute queries with the database.

Programming Example

Select Record in ResultSet using Statement

package JavaStatements; import java.sql.*; public class Statement_Example < static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String dburl = "jdbc:mysql://localhost/STOREDB"; static final String dbuser = "root"; static final String dbpass = "root"; public static void main(String[] args) < Connection con = null; Statement stmt = null; try < //Step 1 : Connecting to server and database con = DriverManager.getConnection(dburl, dbuser, dbpass); //Step 2 : Initialize Statement stmt=con.createStatement(); //Step 3 : SQL Query //Select Query String querySelect="SELECT * FROM ITEM"; ResultSet rset=stmt.executeQuery(querySelect); while(rset.next()) < System.out.println("ID: "+rset.getInt(1) + ", PRODUCT: " + rset.getString(2) + ", Price: " + rset.getString(3)); >> catch (SQLException e) < System.err.println("Cannot connect ! "); e.printStackTrace(); >finally < System.out.println("Closing the connection."); if (con != null) try < con.close(); >catch (SQLException ignore) <> > > >

ID: 1, PRODUCT: MousePad, Price: 190
ID: 2, PRODUCT: Stationary, Price: 2870
ID: 3, PRODUCT: Books, Price: 765
ID: 4, PRODUCT: HardDisk, Price: 3887
ID: 5, PRODUCT: Ram, Price: 953
ID: 6, PRODUCT: Printer, Price: 8746
ID: 7, PRODUCT: Keyboard, Price: 646
ID: 8, PRODUCT: Mouse, Price: 947
ID: 9, PRODUCT: HDMI CABLE, Price: 850
ID: 11, PRODUCT: Charger, Price: 1800
ID: 12, PRODUCT: MainJava Logo, Price: 900
_

Insert Row in Table using Statement

package JavaStatements; import java.sql.*; public class Statement_Example < static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String dburl = "jdbc:mysql://localhost/STOREDB"; static final String dbuser = "root"; static final String dbpass = "root"; public static void main(String[] args) < Connection con = null; Statement stmt = null; try < //Step 1 : Connecting to server and database con = DriverManager.getConnection(dburl, dbuser, dbpass); //Step 2 : Initialize Statement stmt=con.createStatement(); //Step 3 : SQL Query //Insert Query String queryInsert="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('876','Screen Guard')"; stmt.executeUpdate(queryInsert); System.out.println("Row Inserted"); >catch (SQLException e) < System.err.println("Cannot connect ! "); e.printStackTrace(); >finally < System.out.println("Closing the connection."); if (con != null) try < con.close(); >catch (SQLException ignore) <> > > >

Row Inserted
Closing the connection.

Update Record in Table using Statement

package JavaStatements; import java.sql.*; public class Statement_Example < static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String dburl = "jdbc:mysql://localhost/STOREDB"; static final String dbuser = "root"; static final String dbpass = "root"; public static void main(String[] args) < Connection con = null; Statement stmt = null; try < //Step 1 : Connecting to server and database con = DriverManager.getConnection(dburl, dbuser, dbpass); //Step 2 : Initialize Statement stmt=con.createStatement(); //Step 3 : SQL Query //Update Query String queryUpdate="UPDATE ITEM SET PRICE = 455 WHERE PRODUCT = 'Screen Guard'"; stmt.executeUpdate(queryUpdate); System.out.println("Row Updated"); String querySelect="SELECT * FROM ITEM"; ResultSet rset=stmt.executeQuery(querySelect); while(rset.next()) < System.out.println("ID: "+rset.getInt(1) + ", PRODUCT: " + rset.getString(2) + ", Price: " + rset.getString(3)); >> catch (SQLException e) < System.err.println("Cannot connect ! "); e.printStackTrace(); >finally < System.out.println("Closing the connection."); if (con != null) try < con.close(); >catch (SQLException ignore) <> > > >

Output
Row Updated
Closing the connection.

Delete Row in Table using Statement

package JavaStatements; import java.sql.*; public class Statement_Example < static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String dburl = "jdbc:mysql://localhost/STOREDB"; static final String dbuser = "root"; static final String dbpass = "root"; public static void main(String[] args) < Connection con = null; Statement stmt = null; try < //Step 1 : Connecting to server and database con = DriverManager.getConnection(dburl, dbuser, dbpass); //Step 2 : Initialize Statement stmt=con.createStatement(); //Step 3 : SQL Query //Delete Query String queryDelete="DELETE FROM ITEM WHERE PRODUCT = 'Screen Guard'"; stmt.executeUpdate(queryDelete); System.out.println("Row Deleted"); >catch (SQLException e) < System.err.println("Cannot connect ! "); e.printStackTrace(); >finally < System.out.println("Closing the connection."); if (con != null) try < con.close(); >catch (SQLException ignore) <> > > >

Row Deleted
Closing the connection.

Summary

It is very easy to use Statement Interface in JDBC programming for executing queries in database. However, it is not efficient way to run query in database. Instead of Statement you can use PreparedStatement , which is efficient and faster than Statement Object. In the next chapter you will learn about PreparedStatement in JDBC.

Источник

Java JDBC Statement

Интерфейс Java JDBC, java.sql.Statement, используется для выполнения операторов SQL в реляционной базе данных. Вы получаете инструкцию JDBC из соединения JDBC. Если у вас есть экземпляр Java Statement, вы можете выполнить с ним запрос базы данных или обновление базы данных. В этом руководстве покажу как использовать Statement для выполнения запросов, обновлений и как правильно закрыть экземпляр Statement, когда вы закончите работу.

Создать заявление

Чтобы использовать Java JDBC Statement, сначала необходимо создать Statement.

Statement statement = connection.createStatement();

Экземпляр соединения является экземпляром Java JDBC Connection.

Выполнение запроса через оператор

Как только вы создали объект Java Statement, вы можете выполнить запрос к базе данных. Вы делаете это, вызывая его метод executeQuery(), передавая оператор SQL в качестве параметра. Метод Statement executeQuery() возвращает Java JDBC ResultSet, который можно использовать для навигации по ответу на запрос. Вот пример вызова Java JDBC Statement executeQuery() и навигации по возвращенному ResultSet:

String sql = «select * from people»; ResultSet result = statement.executeQuery(sql); while(result.next())

Помните, что ResultSet должен быть закрыт, когда вы закончите с ним.

Выполнить обновление через оператор

Вы также можете выполнить обновление базы данных с помощью экземпляра Java JDBC Statement. Например, вы можете выполнить вставку, обновление или удаление SQL через экземпляр Statement. Вот пример выполнения обновления базы данных с помощью экземпляра Java JDBC Statement:

Statement statement = connection.createStatement(); String sql = "update people set name='John' where rowsAffected = statement.executeUpdate(sql);

RowActed, возвращаемый вызовом Statement.executeUpdate(sql), сообщает, сколько записей в базе данных было затронуто оператором SQL.

Закрытие заявления

Как только вы закончили с экземпляром Statement, вам нужно закрыть его. Вы закрываете экземпляр Statement, вызывая его метод close(). Вот пример закрытия экземпляра Java JDBC Statement:

Закрытие оператора с помощью Java Try

Чтобы правильно закрыть оператор после использования, вы можете открыть его внутри блока Java Try With Resources. Вот пример закрытия экземпляра Java JDBC Statement с использованием конструкции try-with-resources:

try(Statement statement = connection.createStatement()) < //use the statement in here. >catch(SQLException e)

После выхода из блока try оператор Statement будет закрыт автоматически.

Заявление против PreparedStatement

Java JDBC API имеет интерфейс, похожий на оператор под названием PreparedStatement. PreparedStatement может иметь параметры, вставленные в оператор SQL, поэтому PreparedStatement можно использовать снова и снова с другими значениями параметров. Вы не можете сделать это с Заявлением. Оператор требует законченного оператора SQL в качестве параметра.

Источник

JDBC Statement

The Java JDBC Statement, java.sql.Statement , interface is used to execute SQL statements against a relational database. You obtain a JDBC Statement from a JDBC Connection. Once you have a Java Statement instance you can execute either a database query or an database update with it. This Java JDBC Statement tutorial will explain how you use a Statement to execute queries, updates, and how to properly close the Statement instance when you are done with it.

Create Statement

In order to use a Java JDBC Statement you first need to create a Statement . Here is an example of creating a Java Statement instance:

Statement statement = connection.createStatement();

The connection instance is a Java JDBC Connection instance.

Executing a Query via a Statement

Once you have created a Java Statement object, you can execute a query against the database. You do so by calling its executeQuery() method, passing an SQL statement as parameter. The Statement executeQuery() method returns a Java JDBC ResultSet which can be used to navigate the response of the query. Here is an example of calling the Java JDBC Statement executeQuery() and navigating the returned ResultSet :

String sql = «select * from people»; ResultSet result = statement.executeQuery(sql); while(result.next())

Remember that the ResultSet needs to be closed when you are done with it.

Execute an Update via a Statement

You can also execute an update of the database via a Java JDBC Statement instance. For instance, you could execute an SQL insert, update or delete via a Statement instance. Here is an example of executing a database update via a Java JDBC Statement instance:

Statement statement = connection.createStatement(); String sql = "update people set name='John' where rowsAffected = statement.executeUpdate(sql);

The rowsAffected returned by the statement.executeUpdate(sql) call, tells how many records in the database were affected by the SQL statement.

Closing a Statement

Once you are finished with a Statement instance you need to close it. You close a Statement instance by calling its close() method. Here is an example of closing a Java JDBC Statement instance:

Closing a Statement Using Java Try With Resources

In order to close a Statement correctly after use, you can open it inside a Java Try With Resources block. Here is an example of closing a Java JDBC Statement instance using the try-with-resources construct:

try(Statement statement = connection.createStatement()) < //use the statement in here. >catch(SQLException e)

Once the try block exits, the Statement will be closed automatically.

Statement vs. PreparedStatement

The Java JDBC API has an interface similar to the Statement called PreparedStatement . The PreparedStatement can have parameters inserted into the SQL statement, so the PreparedStatement can be reused again and again with different parameter values. You cannot do that with a Statement . A Statement requires a finished SQL statement as parameter.

Источник