Updating database in java

JDBC-Updating database in Java

If we want to update a record in a database from our Java application , the JDBC API provides facilities for the same.Here we are discussing about Updating database in Java .

Updating database in Java

We are using MySQL server community version as database .We can download it from here. For updating some record in database table , we should have a database and a table with some contents. So we need to create data base and database table. The following queries will do the same.You can run those queries either from the default command line client of MySQL client or from an object explorer like SQLyog. The community version of SQLyog is available for learning purpose .We can download it from here.

1)Create database

CREATE DATABASE mydb;

2)Create database table

CREATE TABLE testtable ( number INT, DATA VARCHAR(100) );

Now let us see the Java example to update the existing record.For this code the MySQL connector jar should be there in class path.We can download the MySQL connector from here.

Our Java class DBUpdateSample.java is having two methods apart from the main method.The insert() method inserts a record to the table. The update() method updates the existing record. The database name and password mentioned in the connection url is the user name and password of our database.The insert() method is having the insert query and the update() method is having the update query.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBUpdateSample private Connection connection = null;
private Statement statement = null;
private ResultSet resultset = null;
public DBUpdateSample() >
public void insert() try Class.forName(«com.mysql.jdbc.Driver»);
connection = DriverManager.getConnection(
«jdbc:mysql://localhost:3306/mydb», «username», «password»);
statement = connection.createStatement();

int result = statement
.executeUpdate(«insert into testtable values(‘1′,’cat’)»);
if (result >= 0) String query = «select * from testtable»;
resultset = statement.executeQuery(query);
System.out.println(«Inserted record»);
while (resultset.next()) System.out.println(«Number ; Data update testtable SET data=’animal’ where number=’1′ «);
if (result >= 0) String query = «select * from testtable»;
resultset = statement.executeQuery(query);
System.out.println(«updated record»);
while (resultset.next()) System.out.println(«Number ; Data text-decoration: underline;»>Output

Источник

SQLite Java: Update Data

Summary: this tutorial shows you how to update data in a table from a Java program using JDBC.

To update existing data of a table, you follow the steps below:

1. First, connect to the SQLite database.
2. Next, prepare the UPDATE statement. For the UPDATE statement that uses parameters, you use the question marks (?) placeholder in the SET and WHERE clauses.
3. Then, instantiate an object the PreparedStatement class by calling the prepareStatement() method of the Connection object.
4. After that, set a value for each placeholder using the set* method of the PreparedStatement object, for example, setString() , setInt() , etc.
5. Finally, execute the UPDATE statement by calling the executeUpdate() method of the PreparedStatement object.

The following program inserts three rows into the warehouses table that we created in the creating table tutorial.

package net.sqlitetutorial; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; /** * * @author sqlitetutorial.net */ public class UpdateApp < /** * 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; > /** * Update data of a warehouse specified by the id * * @param id * @param name name of the warehouse * @param capacity capacity of the warehouse */ public void update(int id, String name, double capacity) < String sql = "UPDATE warehouses SET name = ? , " + "capacity = ? " + "WHERE try (Connection conn = this.connect(); PreparedStatement pstmt = conn.prepareStatement(sql)) < // set the corresponding param pstmt.setString(1, name); pstmt.setDouble(2, capacity); pstmt.setInt(3, id); // update pstmt.executeUpdate(); > catch (SQLException e) < System.out.println(e.getMessage()); >> /** * @param args the command line arguments */ public static void main(String[] args) < UpdateApp app = new UpdateApp(); // update the warehouse with id 3 app.update(3, "Finished Products", 5500); > > Code language: Java (java)

First, you check the warehouse table before running the update program using the following SELECT statement.

SELECT id, name, capacity FROM warehouses;Code language: SQL (Structured Query Language) (sql)

Second, execute the UpdateApp program.

Third, query data from the warehouses table again using the same SELECT statement above.

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

Источник

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

Источник