Inserting Data into SQL Database with Java

Inserting Data into SQL Database with Java

Java Documentation - Processing SQL statements with JDBC 

Process:

1. Get connection to database
2. Create a statement
3. Execute SQL query

import java.sql.*;

/**
 *
 * @author www.luv2code.com
 *
 */
public class JdbcTest {

public static void main(String[] args) throws SQLException {

Connection myConn = null;
Statement myStmt = null;
ResultSet myRs = null;

String dbUrl = "jdbc:mysql://localhost:3306/demo?autoReconnect=true&useSSL=false";
String user = "root";
String pass = "Sasanka01";

try {
// 1. Get a connection to database
myConn = DriverManager.getConnection(dbUrl, user, pass);
System.out.println("Database connection successfully created");

// 2. Create a statement
myStmt = myConn.createStatement();

int rowsAffected = myStmt.executeUpdate(
                         "insert into employees " +
                         "(last_name, first_name, email, department, salary)"+
                         "values" +
                          "('Wright', 'Eric', 'eric.wright@foo.com', 'HR',33000.00)");

//System.out.println(rowsAffected);

// 3. Execute SQL query
myRs = myStmt.executeQuery("select * from employees");

// 4. Process the result set
while (myRs.next()) {
System.out.println(myRs.getString("last_name") + ", " + myRs.getString("first_name"));
}
}
catch (Exception exc) {
exc.printStackTrace();
}
finally {
if (myRs != null) {
myRs.close();
}

if (myStmt != null) {
myStmt.close();
}

if (myConn != null) {
myConn.close();
}
}
}

}

-------------------------

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, CoffeesTables.viewTable creates a Statement object with the following code:
stmt = con.createStatement();


There are three different kinds of statements:

1. Statement: Used to implement simple SQL statements with no parameters.
2. PreparedStatement: (Extends Statement.) Used for precompiling SQL statements that might contain input parameters. See Using Prepared Statementsfor more information.
3. 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:

1. 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.
2. executeQuery: Returns one ResultSet object.
3. 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, CoffeesTables.viewTable executed a Statement object with the following code:
ResultSet rs = stmt.executeQuery(query);

-------------------

INSERT query

int rowsAffected = myStmt.executeUpdate(
                         "insert into employees " +
                         "(last_name, first_name, email, department, salary)"+
                         "values" +
                          "('Wright', 'Eric', 'eric.wright@foo.com', 'HR',33000.00)");

DELETE query

int rowsAffected = myStmt.executeUpdate(

"delete from employees " + 

"where last_name='Wright' and first_name= 'Eric'");

System.out.println(rowsAffected);