Главная » Языки программирования

Java sql запросы update

Содержание
  1. UPDATE records in a table with JDBC
  2. Steps to update records in a table with UPDATE SQL statement using JDBC —
  3. Updating data in database table using JDBC
  4. Output
  5. Java JDBC CRUD Tutorial: SQL Insert, Select, Update, and Delete Examples
  6. 1. Prerequisites
  7. 2. Creating a sample MySQL database
  8. 3. Understand the main JDBC interfaces and classes
  9. 4. Connecting to the database
  10. 5. JDBC Execute INSERT Statement Example
  11. 6. JDBC Execute SELECT Statement Example
  12. 7. JDBC Executing UPDATE Statement Example
  13. 8. JDBC Execute DELETE Statement Example
  14. JDBC API References:
  15. Related JDBC Tutorials:
  16. About the Author:

UPDATE records in a table with JDBC

The JDBC classes and interfaces are available in java.sql and javax.sql packages.

Steps to update records in a table with UPDATE SQL statement using JDBC —

Class.forName("oracle.jdbc.driver.OracleDriver");

In the upcoming example, we are going to create a table within the Oracle database using its JDBC driver. 

Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott", "tiger");
  • 1521 is the general port number to connect to the database.
  • XE stands for Oracle Express Edition Database.
  • scott is our username to connect to the Oracle database and tiger is the password.
Statement stmt = con.createStatement();

Creating a Statement object to using createStatement() method of Connection interface.

int count = stmt.executeUpdate(query);

count gives us the total number of rows updated in a database table due to the execution of a SQL query using executeUpdate() method, where query is a String object which specifies the SQL query to execute.

con is a Connection reference used to close the connection with the database after the updation is over.

Updating data in database table using JDBC

 import java.sql.*; class A < public static void main(String. ar) < try < //First SQL UPDATE Query to update record. String query1 = "Update MyTable2 Set FirstName='Thomas' Where FirstName = 'Tom'"; //Second SQL UPDATE Query to update record. String query2 = "Update MyTable2 Set FirstName='Bradly' where age = '53'"; //Third SQL SELECT Query to retrieve updated records. String query3 = "SELECT * FROM MyTable2"; Class.forName("oracle.jdbc.driver.OracleDriver"); Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","System", "Promila21"); Statement stmt = con.createStatement(); //Executing first SQL UPDATE query using executeUpdate() method of Statement object. int count = stmt.executeUpdate(query1); System.out.println("Number of rows updated by executing query1 = " + count); //Executing second SQL UPDATE query using executeUpdate() method of Statement object. count = stmt.executeUpdate(query2); System.out.println("Number of rows updated by executing query2 = " + count); //Executing SQL SELECT query using executeQuery() method of Statement object. ResultSet rs = stmt.executeQuery(query3); System.out.println("Result of executing query3 to display updated records"); System.out.println("ID " + "\t" + "FirstName" + "\t" + "LastName" + "\t" + "Age"); //looping through the number of row/rows retrieved after executing SELECT query3 while(rs.next()) < System.out.print(rs.getString("ID") + "\t"); System.out.print(rs.getString("FirstName") + "\t" + "\t"); System.out.print(rs.getString("LastName")+ "\t" + "\t"); System.out.println(rs.getString("Age") + "\t"); >> catch(SQLException e) < System.out.println(e); >>//main() method ends >//class definitin ends

Output

Number of rows updated by executing query1 = 1 Number of rows updated by executing query2 = 1 Result of executing query3 to display updated records ID FirstName LastName Age 1 Thomas Hanks 61 2 Johnny Depp 54 3 Bradly Pitt 53

    In the output, you may see the updated records in table MyTable, where FirstName Tom is changed to Thomas and Brad is changed to Bradley.

ID FirstName LastName Age
1 Thomas Hanks 61
2 Johnny Depp 54
3 Bradley Pitt 53

Table — MyTable

Источник

Java JDBC CRUD Tutorial: SQL Insert, Select, Update, and Delete Examples

This JDBC tutorial is going to help you learning how to do basic database operations (CRUD — Create, Retrieve, Update and Delete) using JDBC (Java Database Connectivity) API. These CRUD operations are equivalent to the INSERT, SELECT, UPDATE and DELETE statements in SQL language. Although the target database system is MySQL, but the same technique can be applied for other database systems as well because the query syntax used is standard SQL which is supported by all relational database systems.

We will learn how to do insert, query, update and delete database records by writing code to manage records of a table Users in a MySQL database called SampleDB.

Table of content:

1. Prerequisites

    • JDK (download JDK 7).
    • MySQL (download MySQL Community Server 5.6.12). You may also want to download MySQL Workbench — a graphical tool for working with MySQL databases.
    • JDBC Driver for MySQL (download MySQL Connector/J 5.1.25). Extract the zip archive and put the mysql-connector-java-VERSION-bin.jar file into classpath (in a same folder as your Java source files).

    2. Creating a sample MySQL database

    Let’s create a MySQL database called SampleDB with one table Users with the following structure:

    Users table structures

    Execute the following SQL script inside MySQL Workbench:

    create database SampleDB; use SampleDB; CREATE TABLE `users` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(45) NOT NULL, `password` varchar(45) NOT NULL, `fullname` varchar(45) NOT NULL, `email` varchar(45) NOT NULL, PRIMARY KEY (`user_id`) );

    Or if you are using MySQL Command Line Client program, save the above script into a file, let’s say, SQLScript.sql and execute the following command:

    source Path\To\The\Script\File\SQLScript.sql

    Here’s an example screenshot taken while executing the above script in MySQL Command Line Client program:

    execute SQL script

    3. Understand the main JDBC interfaces and classes

    Let’s take an overview look at the JDBC’s main interfaces and classes with which we usually work. They are all available under the java.sql package:

      • DriverManager : this class is used to register driver for a specific database type (e.g. MySQL in this tutorial) and to establish a database connection with the server via its getConnection() method.
      • Connection : this interface represents an established database connection (session) from which we can create statements to execute queries and retrieve results, get metadata about the database, close connection, etc.
      • Statement and PreparedStatement : these interfaces are used to execute static SQL query and parameterized SQL query, respectively. Statement is the super interface of the PreparedStatement interface. Their commonly used methods are:
          • boolean execute(String sql) : executes a general SQL statement. It returns true if the query returns a ResultSet , false if the query returns an update count or returns nothing. This method can be used with a Statement only.
          • int executeUpdate(String sql) : executes an INSERT, UPDATE or DELETE statement and returns an update account indicating number of rows affected (e.g. 1 row inserted, or 2 rows updated, or 0 rows affected).
          • ResultSet executeQuery(String sql) : executes a SELECT statement and returns a ResultSet object which contains results returned by the query.

      A prepared statement is one that contains placeholders (in form question marks ?) for dynamic values will be set at runtime. For example:

      SELECT * from Users WHERE user_id=?

      Here the value of user_id is parameterized by a question mark and will be set by one of the setXXX() methods from the PreparedStatement interface, e.g. setInt(int index, int value) .

        • ResultSet : contains table data returned by a SELECT query. Use this object to iterate over rows in the result set using next() method, and get value of a column in the current row using getXXX() methods (e.g. getString() , getInt() , getFloat() and so on). The column value can be retrieved either by index number (1-based) or by column name.
        • SQLException : this checked exception is declared to be thrown by all the above methods, so we have to catch this exception explicitly when calling the above classes’ methods.

        4. Connecting to the database

        Supposing the MySQL database server is listening on the default port 3306 at localhost. The following code snippet connects to the database name SampleDB by the user root and password secret:

        String dbURL = «jdbc:mysql://localhost:3306/sampledb»; String username = «root»; String password = «secret»; try < Connection conn = DriverManager.getConnection(dbURL, username, password); if (conn != null) < System.out.println("Connected"); >> catch (SQLException ex)

        Once the connection was established, we have a Connection object which can be used to create statements in order to execute SQL queries. In the above code, we have to close the connection explicitly after finish working with the database:

        However, since Java 7, we can take advantage of the try -with-resources statement which will close the connection automatically, as shown in the following code snippet:

        try (Connection conn = DriverManager.getConnection(dbURL, username, password)) < // code to execute SQL queries goes here. >catch (SQLException ex)

        If you are using Java 7 or later, this approach is recommended. The sample programs in this tutorial are all using this try-with-resources statement to make a database connection.

        NOTE: For details about connecting to a MySQL database, see the article: Connect to MySQL database via JDBC.

        5. JDBC Execute INSERT Statement Example

          • username: bill
          • password: secretpass
          • fullname: Bill Gates
          • email: bill.gates@microsoft.com

          String sql = «INSERT INTO Users (username, password, fullname, email) VALUES (?, ?, ?, ?)»; PreparedStatement statement = conn.prepareStatement(sql); statement.setString(1, «bill»); statement.setString(2, «secretpass»); statement.setString(3, «Bill Gates»); statement.setString(4, «bill.gates@microsoft.com»); int rowsInserted = statement.executeUpdate(); if (rowsInserted > 0)

          In this code, we create a parameterized SQL INSERT statement and create a PreparedStatement from the Connection object. To set values for the parameters in the INSERT statement, we use the PreparedStatement ‘s setString() methods because all these columns in the table Users are of type VARCHAR which is translated to String type in Java. Note that the parameter index is 1-based (unlike 0-based index in Java array).

          The PreparedStatement interface provides various setXXX() methods corresponding to each data type, for example:

Оцените статью
© 2024 Программирование