JDBC Statement Example – Update A Record

Here’s an example to show you how to update a record in a table via JDBC statement. To issue a update statement, calls the Statement.executeUpdate() method like this :

Statement statement = dbConnection.createStatement();
// execute the update SQL stetement
statement.executeUpdate(updateTableSQL);

Full example…

package com.mkyong.jdbc;
 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
 
public class JDBCStatementUpdateExample {
 
 private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
 private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:MKYONG";
 private static final String DB_USER = "user";
 private static final String DB_PASSWORD = "password";
 
 public static void main(String[] argv) {
 
  try {
 
   updateRecordIntoDbUserTable();
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  }
 
 }
 
 private static void updateRecordIntoDbUserTable() throws SQLException {
 
  Connection dbConnection = null;
  Statement statement = null;
 
  String updateTableSQL = "UPDATE DBUSER"
    + " SET USERNAME = 'mkyong_new' "
    + " WHERE USER_ID = 1";
 
  try {
   dbConnection = getDBConnection();
   statement = dbConnection.createStatement();
 
   System.out.println(updateTableSQL);
 
   // execute update SQL stetement
   statement.execute(updateTableSQL);
 
   System.out.println("Record is updated to DBUSER table!");
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  } finally {
 
   if (statement != null) {
    statement.close();
   }
 
   if (dbConnection != null) {
    dbConnection.close();
   }
 
  }
 
 }
 
 private static Connection getDBConnection() {
 
  Connection dbConnection = null;
 
  try {
 
   Class.forName(DB_DRIVER);
 
  } catch (ClassNotFoundException e) {
 
   System.out.println(e.getMessage());
 
  }
 
  try {
 
   dbConnection = DriverManager.getConnection(
                              DB_CONNECTION, DB_USER,DB_PASSWORD);
   return dbConnection;
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  }
 
  return dbConnection;
 
 }
 
}

Result

The username of “user_id = 1″ is updated to a new value ‘mkyong_new’.

UPDATE DBUSER SET USERNAME = 'mkyong_new'  WHERE USER_ID = 1
Record IS updated INTO DBUSER TABLE!

Read More

JDBC Statement Example – Insert A Record

Here’s an example to show you how to insert a record into table via JDBC statement. To issue a insert statement, calls the Statement.executeUpdate() method like this :

Statement statement = dbConnection.createStatement();
// execute the insert SQL stetement
statement.executeUpdate(insertTableSQL);

Full example…

package com.mkyong.jdbc;
 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
 
public class JDBCStatementInsertExample {
 
 private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
 private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:MKYONG";
 private static final String DB_USER = "user";
 private static final String DB_PASSWORD = "password";
 private static final DateFormat dateFormat = new SimpleDateFormat(
   "yyyy/MM/dd HH:mm:ss");
 
 public static void main(String[] argv) {
 
  try {
 
   insertRecordIntoDbUserTable();
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  }
 
 }
 
 private static void insertRecordIntoDbUserTable() throws SQLException {
 
  Connection dbConnection = null;
  Statement statement = null;
 
  String insertTableSQL = "INSERT INTO DBUSER"
    + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) " + "VALUES"
    + "(1,'mkyong','system', " + "to_date('"
    + getCurrentTimeStamp() + "', 'yyyy/mm/dd hh24:mi:ss'))";
 
  try {
   dbConnection = getDBConnection();
   statement = dbConnection.createStatement();
 
   System.out.println(insertTableSQL);
 
   // execute insert SQL stetement
   statement.executeUpdate(insertTableSQL);
 
   System.out.println("Record is inserted into DBUSER table!");
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  } finally {
 
   if (statement != null) {
    statement.close();
   }
 
   if (dbConnection != null) {
    dbConnection.close();
   }
 
  }
 
 }
 
 private static Connection getDBConnection() {
 
  Connection dbConnection = null;
 
  try {
 
   Class.forName(DB_DRIVER);
 
  } catch (ClassNotFoundException e) {
 
   System.out.println(e.getMessage());
 
  }
 
  try {
 
   dbConnection = DriverManager.getConnection(
                               DB_CONNECTION, DB_USER,DB_PASSWORD);
   return dbConnection;
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  }
 
  return dbConnection;
 
 }
 
 private static String getCurrentTimeStamp() {
 
  java.util.Date today = new java.util.Date();
  return dateFormat.format(today.getTime());
 
 }
 
}

Result

A record is inserted into a table named “DBUSER”.

INSERT INTO DBUSER(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) 
VALUES(1,'mkyong','system', to_date('2011/04/04 13:59:03', 'yyyy/mm/dd hh24:mi:ss'))
Record IS inserted INTO DBUSER TABLE!

Read More

JDBC Statement Example – Create A Table

Here’s an example to show you how to create a table in database via JDBC statement. To issue a create statement, calls the Statement.execute() method like this :

Statement statement = dbConnection.createStatement();
// execute create SQL stetement
statement.execute(createTableSQL);

Full example…

package com.mkyong.jdbc;
 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
 
public class JDBCStatementCreateExample {
 
 private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
 private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:MKYONG";
 private static final String DB_USER = "user";
 private static final String DB_PASSWORD = "password";
 
 public static void main(String[] argv) {
 
  try {
 
   createDbUserTable();
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  }
 
 }
 
 private static void createDbUserTable() throws SQLException {
 
  Connection dbConnection = null;
  Statement statement = null;
 
  String createTableSQL = "CREATE TABLE DBUSER("
    + "USER_ID NUMBER(5) NOT NULL, "
    + "USERNAME VARCHAR(20) NOT NULL, "
    + "CREATED_BY VARCHAR(20) NOT NULL, "
    + "CREATED_DATE DATE NOT NULL, " + "PRIMARY KEY (USER_ID) "
    + ")";
 
  try {
   dbConnection = getDBConnection();
   statement = dbConnection.createStatement();
 
   System.out.println(createTableSQL);
                        // execute the SQL stetement
   statement.execute(createTableSQL);
 
   System.out.println("Table \"dbuser\" is created!");
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  } finally {
 
   if (statement != null) {
    statement.close();
   }
 
   if (dbConnection != null) {
    dbConnection.close();
   }
 
  }
 
 }
 
 private static Connection getDBConnection() {
 
  Connection dbConnection = null;
 
  try {
 
   Class.forName(DB_DRIVER);
 
  } catch (ClassNotFoundException e) {
 
   System.out.println(e.getMessage());
 
  }
 
  try {
 
   dbConnection = DriverManager.getConnection(
     DB_CONNECTION, DB_USER,DB_PASSWORD);
   return dbConnection;
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  }
 
  return dbConnection;
 
 }
 
}

Result

Here’s the result.

CREATE TABLE DBUSER(
  USER_ID NUMBER(5) NOT NULL, 
  USERNAME VARCHAR(20) NOT NULL, 
  CREATED_BY VARCHAR(20) NOT NULL, 
  CREATED_DATE DATE NOT NULL, 
  PRIMARY KEY (USER_ID) 
)
TABLE "user" IS created!

Read More

Connect To Oracle DB Via JDBC Driver

Here is an example to show you how to connect to Oracle database via JDBC driver.
1. Download Oracle JDBC Driver

Get Oracle JDBC driver here – ojdbcxxx.jar

2. Java JDBC connection example

Code snippets to use JDBC to connect a Oracle database.

Class.forName("org.postgresql.Driver");
Connection connection = null;
connection = DriverManager.getConnection(
 "jdbc:oracle:thin:@localhost:1521:mkyong","username","password");
connection.close();

See a complete example below :
File : OracleJDBC.java

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
 
public class OracleJDBC {
 
 public static void main(String[] argv) {
 
  System.out.println("-------- Oracle JDBC Connection Testing ------");
 
  try {
 
   Class.forName("oracle.jdbc.driver.OracleDriver");
 
  } catch (ClassNotFoundException e) {
 
   System.out.println("Where is your Oracle JDBC Driver?");
   e.printStackTrace();
   return;
 
  }
 
  System.out.println("Oracle JDBC Driver Registered!");
 
  Connection connection = null;
 
  try {
 
   connection = DriverManager.getConnection(
     "jdbc:oracle:thin:@localhost:1521:mkyong", "username",
     "password");
 
  } catch (SQLException e) {
 
   System.out.println("Connection Failed! Check output console");
   e.printStackTrace();
   return;
 
  }
 
  if (connection != null) {
   System.out.println("You made it, take control your database now!");
  } else {
   System.out.println("Failed to make connection!");
  }
 }
 
}

2. Run it

Assume OracleJDBC.java is store in “C:\jdbc-test” folder, together with Oracle JDBC driver (ojdbc6.jar), then run following commands :

C:\jdbc-test>javac OracleJDBC.java
 
C:\jdbc-test>java -cp c:\jdbc-test\ojdbc6.jar;c:\jdbc-test OracleJDBC
-------- Oracle JDBC Connection Testing ------------
Oracle JDBC Driver Registered!
You made it, take control your database now!
 
C:\jdbc-test>

Done.

Read More

Connect To PostgreSQL With JDBC Driver

Here is an example to show you how to connect to PostgreSQL database with JDBC driver.

1. Download PostgreSQL JDBC Driver

Get a PostgreSQL JDBC driver at this URL : http://jdbc.postgresql.org/download.html

2. Java JDBC connection example

Code snippets to use JDBC to connect a PostgreSQL database

Class.forName("org.postgresql.Driver");
Connection connection = null;
connection = DriverManager.getConnection(
   "jdbc:postgresql://hostname:port/dbname","username", "password");
connection.close();

See a complete example below :
File : JDBCExample.java

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
 
public class JDBCExample {
 
 public static void main(String[] argv) {
 
  System.out.println("-------- PostgreSQL "
    + "JDBC Connection Testing ------------");
 
  try {
 
   Class.forName("org.postgresql.Driver");
 
  } catch (ClassNotFoundException e) {
 
   System.out.println("Where is your PostgreSQL JDBC Driver? "
     + "Include in your library path!");
   e.printStackTrace();
   return;
 
  }
 
  System.out.println("PostgreSQL JDBC Driver Registered!");
 
  Connection connection = null;
 
  try {
 
   connection = DriverManager.getConnection(
     "jdbc:postgresql://127.0.0.1:5432/testdb", "mkyong",
     "123456");
 
  } catch (SQLException e) {
 
   System.out.println("Connection Failed! Check output console");
   e.printStackTrace();
   return;
 
  }
 
  if (connection != null) {
   System.out.println("You made it, take control your database now!");
  } else {
   System.out.println("Failed to make connection!");
  }
 }
 
}

3. Run it

Assume JDBCExample is store in c:\test folder, together with PostgreSQL JDBC driver, then run it :

C:\test>java -cp c:\test\postgresql-8.3-603.jdbc4.jar;c:\test JDBCExample
-------- MySQL JDBC Connection Testing ------------
PostgreSQL JDBC Driver Registered!
You made it, take control your database now!

Done

Read More

Connect To MySQL With JDBC Driver

Here’s an example to show you how to connect to MySQL database via a JDBC driver. First, get a MySQL JDBC driver from here -MySQL JDBC Driver Download Here.

1. Java JDBC connection example

Code snippets to use a JDBC driver to connect a MySQL database.

Class.forName("com.mysql.jdbc.Driver");
Connection conn = null;
conn = DriverManager.getConnection("jdbc:mysql://hostname:port/dbname","username", "password");
conn.close();

See a complete example below :

JDBCExample.java

package com.mkyong.common;
 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
 
public class JDBCExample {
 
  public static void main(String[] argv) {
 
 System.out.println("-------- MySQL JDBC Connection Testing ------------");
 
 try {
  Class.forName("com.mysql.jdbc.Driver");
 } catch (ClassNotFoundException e) {
  System.out.println("Where is your MySQL JDBC Driver?");
  e.printStackTrace();
  return;
 }
 
 System.out.println("MySQL JDBC Driver Registered!");
 Connection connection = null;
 
 try {
  connection = DriverManager
  .getConnection("jdbc:mysql://localhost:3306/mkyongcom","root", "password");
 
 } catch (SQLException e) {
  System.out.println("Connection Failed! Check output console");
  e.printStackTrace();
  return;
 }
 
 if (connection != null) {
  System.out.println("You made it, take control your database now!");
 } else {
  System.out.println("Failed to make connection!");
 }
  }
}

2. Run it

Assume JDBCExample.java is store in c:\test folder, along with the MySQL JDBC driver

C:\test>java -cp c:\test\mysql-connector-java-5.1.8-bin.jar;c:\test JDBCExample
-------- MySQL JDBC Connection Testing ------------
MySQL JDBC Driver Registered!
You made it, take control your database now!
 
C:\test>

P.S To run this example, your need mysql-connector-java-{version}-bin.jar in your classpath.

Done.

Read More

Login and Registration using JSP , MS Access Database

As per too many requests am going to explain detail database connection using jsp and ms access .Java server pages are dynamic so we are using it for db connections.Coming to scripting jsp is most flexible and reliable than others,but present php leading good role in web designing..................

Last Posts:

1)Oracle Database Connection 
2)MS Access Database Connection 

HTML CODE PIC FOR LOGIN :

JSP CODE FOR LOGIN.JSP:   

here we used request.getParameter   to get values from one session to another. I used try , catch method when an error occur it will goes to ctach field.

Code designed by #Siddhu Vydyabhushana Subscribe Me


<%@ page import="java.sql.*"%>
<%
try
    {
        String user2=request.getParameter("username");
        String pass2=request.getParameter("password");

  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Connection con=DriverManager.getConnection("jdbc:odbc:epoll","","");
  Statement stmt=con.createStatement();
  ResultSet rs=stmt.executeQuery("select pass from user where user='"+user2+"' and pass='"+pass2+"'");
  if(rs.next())
  {
  String pass3=rs.getString("pass");
  String flag1=rs.getString("flag");
  if(flag1.equals(str)==true)
  {
  
  if (pass3.equals(pass2)==true)
  {
  session.putValue("user1",user2);
  response.sendRedirect("user.jsp");
  }
  }
  }
  else
  {%>
  <script>
  alert("invalid login");
  response.sendRedirect("epoll.html");

  </script>
  
  <%}
  con.close();
  }
catch(Exception e)
    {
        out.println("done exception"+e);
  
    }

HTML PIC FOR SIGNUP:-

signup.jsp code:   here we used request.getParameter   to get values from one session to another. I used try , catch method when an error occur it will goes to ctach field.

Code designed by #Siddhu Vydyabhushana Subscribe Me

<%@ page import="java.sql.*"%>
<%
try
    {
        String user1=request.getParameter("username");
        String email1=request.getParameter("email");
        String pass1=request.getParameter("password");
  int  flag=0;
  String str=Integer.toString(flag);
  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Connection con=DriverManager.getConnection("jdbc:odbc:epoll","","");
  PreparedStatement pst= con.prepareStatement("insert into user values(?,?,?,?)");
  pst.setString(1,user1);
  pst.setString(2,email1);
  pst.setString(3,pass1);
  pst.setString(4,str);
  
  pst.executeUpdate();
  
  con.close();
    }
catch(Exception e)
    {
        out.println("done exception"+e);
  
    }
%>

Read More

Simple Disappear effect using jquery

Now let us see a new concept in Jquery

This sample will show you how to make something disappear when an image button is clicked.

Required Files:

1. disappear.html
2.jquery-1.4.2.js



When the <img class="delete"> is clicked, it will find its parent element <div class="pane"> and animate its opacity=hide with slow speed.

Here i am placing source code... 

DOWNLOAD SOURCE CODE

DOWNLOAD jquery-1.4.2.js

Read More

Auto Complete Text box using jquery with oracle database and JSP

Required Files:

• index.jsp

• list.jsp

• jquery.autocomplete.js

• jquery-1.4.2.min.js

• style.css

Database:

create a user with name "ex" and password as "ex"create a table as "states" place all your required states in that table

Source Files:

Here i am giving you a link to DOWNLOAD these five files 

How to run:

copy these five files and paste it into C:\Program Files\Apache Software Foundation\Tomcat 7.0\webapps\yourfolder

run it as http://localhost:8001/index.jsp
type just one character like 'a' then you find all the states starts with "a".

How the source code is working:

See in list.jsp line no.33 you may get one doubt, what is that ‘q‘ right ? in fact we are not sending any parameter with name ‘q’ from the index.jsp but for every keyup in the text box (in index.jsp) jquery will sends each character to the list.jsp in the form of ‘q’ and compares with the values in that list, i mean ‘q’ is the default parameter using by jQuery API.

How to send extra parameters along with that q:

$(‘#country’).autocomplete(“list.jsp”, {extraParams: {state: California }} );

This will generates the internal URL like

……./getdata.jsp?q=ourChar&state=California

Output:

Database:

Table name: states

Read More