Showing posts with label Database. Show all posts

What is SQL Injection and how to avoid it in Java?

SQL Injection
Its a technique where attacker try to alter(modify/change) your SQL query using input parameters.
SQL injection may leads to unexpected transaction (i.e select, update, delete, etc...). We'll see the basic SQL injection examples and later on see how to prevent it using Prepared Statement, Hibernate Criteria and HQL.

Source code (SQLInjection.java)
import java.util.ArrayList;
import java.util.List;

/**
 * Example of SQL injection.
 * @author javaQuery
 * @date 8th November, 2016
 * @Github: https://github.com/javaquery/Examples
 */
public class SQLInjection {

    public static void main(String[] args) {
        /* You are getting parameter value from web page or other user input */
        String parameter = "12"; // normal condition
        new SQLInjection().getUser(parameter);

        /**
         * SQL injection using parameter value. 
         * - If user can change parameter in url, use some script, etc...
         */
        parameter = "12 or 1 = 1";
        new SQLInjection().getUser(parameter);
    }

    /**
     * Get user from database.
     * @param id
     * @return 
     */
    public List<Object> getUser(String id) {
        List<Object> result = new ArrayList<Object>();

        String sql = "SELECT * FROM users WHERE id = " + id + ";";
        System.out.println("SQL Query: " + sql);

        /* prepare connection and execute query */
        return result;
    }
}
Output
In following queries, 1st query is valid and return result as expected but when 2nd query is executed it'll select all users from database and that may leads to unexpected behavior of your system.
SQL Query: SELECT * FROM users WHERE id = 12;
SQL Query: SELECT * FROM users WHERE id = 12 or 1 = 1;
In this example I used user table and this table contains very few records 1k, 10k, etc... but
What if you are selecting data from table which contains millions of records? - Answer is SYSTEM CRASH

Other ways of SQL injection
Consider you are getting value of username and password from parameter into param_username and param_password.
String param_username = "\" or \"\"=\"";
String param_password = "\" or \"\"=\"";

//SQL Injection:
String sql = "SELECT * FROM users WHERE username = \"" + param_username + "\" AND password = \"" + param_password +"\"";
System.out.println(sql);
//OUTPUT: SELECT * FROM users WHERE username = "" or ""="" AND password = "" or ""=""

============================================
String param_userid = "123; DROP TABLE messages;";

//SQL Injection:
String sql = "SELECT * FROM users WHERE id = " + param_userid;
System.out.println(sql);
//OUTPUT: SELECT * FROM users WHERE id = 123; DROP TABLE messages;

First and foremost way: Handle Datatypes
For the sake of simplicity developers don't handle data types at coding. In above code I used String as input parameter in method getUser but should've use Integer/Long. If I used Integer or Long then I've to convert String => 12 or 1 = 1 to Integer/Long => Not Valid Number. It'll prevent SQL Injection.


Avoid SQL Injection using Prepared Statement
Prepared Statement doesn't append values in your SQL query rather it provide SQL query and parameter values separately to database. Database will take care of every parameter value for escape character, special character and every other precaution needed.

Source code (PreparedStatementExample.java)
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Example of SQL injection.
 * @author javaQuery
 * @date 8th November, 2016
 * @Github: https://github.com/javaquery/Examples
 */
public class PreparedStatementExample {
    public static void main(String[] args) {
        new PreparedStatementExample().getUser("12");
    }
    
    /**
     * Get user from database.
     * @param id
     * @return 
     */
    public List<Object> getUser(String id) {
        List<Object> result = new ArrayList<Object>();

        String sql = "SELECT * FROM users where id = ?;";

        /* prepare connection and execute query */
        try {
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo", "root", "root");
            PreparedStatement prepareStatement = connection.prepareStatement(sql);
            prepareStatement.setInt(1,Integer.parseInt(id)); // index of ? is '1', perform null/number check for 'id'
            //execute prepared statement
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        return result;
    } 
}
Output
With PreparedStatement only following query can be generated.
SELECT * FROM users where id = 12;
prepareStatement.setInt: 12 or 1 = 1 is passed as value then Integer.parseInt will throw java.lang.NumberFormatException: For input string: "12 or 1=1".
prepareStatement.setString: What happen if String is used for Number data type in MySQL?


Avoid SQL Injection using Hibernate Criteria
Hibernate Criteria internally uses Prepared Statement to execute query.

Source code
String param_id = "12";

Criteria criteria = session.createCriteria(User.class);
/**
 * 'param_id' provided as String but 'id' declared as Integer/Long in User.java
 * So it'll throw exception(java.lang.String cannot be cast to java.lang.Integer) for invalid data type. (SQL injection handled)
 */
criteria.add(Restrictions.eq("id", param_id));
User user = criteria.uniqueResult();

==============================

// valid query
Integer param_id = 12;

Criteria criteria = session.createCriteria(User.class);
criteria.add(Restrictions.eq("id", param_id));
User user = criteria.uniqueResult();

Avoid SQL Injection using HQL
Its same as Hibernate Criteria.

Source code
String param_id = "12";

Query query = session.createQuery("FROM User WHERE id = :param_id");
/**
 * 'param_id' provided as String but 'id' declared as Integer/Long in User.java
 * So it'll throw exception(java.lang.String cannot be cast to java.lang.Integer) for invalid data type. (SQL injection handled)
 */
query.setParameter("param_id", param_id);
query.list();

==============================

// valid query
Integer param_id = 12;

Query query = session.createQuery("FROM User WHERE id = :param_id");
query.setParameter("param_id", param_id);
query.list();

Microsoft SQL Function : lastIndexOf

I was working around Stored Procedure in MSSQL mean while I get to know that there ain't any function called lastIndexOf(). So I coded it and thought let me share it with you guys. Its one time execution for each database and you can use it in queries, stored procedure, etc...

lastindexof , database

CREATE FUNCTION [dbo].[lastIndexOf] (@String VARCHAR(max) ,@Find VARCHAR(100))
RETURNS INT
AS
BEGIN
 DECLARE @SPLITTED_STRING VARCHAR(500);
 DECLARE @INDEX INT;
 DECLARE @PREVIOUS_INDEX INT;
 DECLARE @LEN INT;
 
 SET @INDEX = 0;
 SET @PREVIOUS_INDEX = 0;
 SET @SPLITTED_STRING = @String;
 
 WHILE ((SELECT CHARINDEX(@Find, @SPLITTED_STRING)) > 0)
  BEGIN
   SELECT @PREVIOUS_INDEX = CHARINDEX(@Find, @SPLITTED_STRING);
   SELECT @LEN = LEN(@SPLITTED_STRING);
   SELECT @SPLITTED_STRING = SUBSTRING(@SPLITTED_STRING, @PREVIOUS_INDEX+1, @LEN);
   SET @INDEX = @PREVIOUS_INDEX + @INDEX;
  END
  
 RETURN @INDEX - 1;
END

How to run this script in MSSQL?
Just copy the above script and paste it in SQL query editor. Press F5 or execute it.

How to use lastIndexOf function?
To get index of specified text execute your query like...
DECLARE @location int;
SET @location = [dbo].[lastIndexOf] ('ABCXYZ','C');
PRINT @location
//output : 2
DECLARE @location int;
SET @location = [dbo].[lastIndexOf] ('ABCXYZ','A');
PRINT @location
//output : 0
DECLARE @location int;
SET @location = [dbo].[lastIndexOf] ('ABCXYZ','F');
PRINT @location
//output : -1
DECLARE @location int;
SET @location = [dbo].[lastIndexOf] ('ABCXYZ','AT');
PRINT @location
//output : -1

How to create log file from/for Stored Procedure. Microsoft SQL Server

What is log file?
If you are experienced programmer then you know importance of log file. Beginner must understand the importance of log file. Log file is like light in the middle of dark jungle. Log file contains the information of any execution that you are working around. It doesn't matter you are working around computer or some machine in car manufacture unit.

Below example is for 'Microsoft SQL Server'. Change variables and input filename as per your requirement. If you want it in your stored procedure just append below code with in stored procedure.
Log file , Stored Procedure, Microsoft SQL Server


------------------------------- At the starting of Stored Procedure -------------------------------
USE [NAME_OF_DATABASE]
GO

/*
 * Microsoft SQL Server
 * Stored Procedure : logExample.sql
 * Date of Creation : 1st Aug, 2013  
 * @author: Vicky Thakor
**/
/*
 * This option specifies the setting for ANSI NULL comparisons. 
 * When this is on, any query that compares a value with a null returns a 0. 
 * When off, any query that compares a value with a null returns a null value.
 **/
SET ANSI_NULLS ON
GO
/*
 * This options specifies the setting for usage of double quotation. 
 * When this is on, double quotation mark is used as part of the SQL Server identifier (object name). 
 * This can be useful in situations in which identifiers are also SQL Server reserved words.
 **/
SET QUOTED_IDENTIFIER ON
GO

/* Start: Log Creation Settings */
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
/* End: Log Creation Settings */
------------------------------- At the starting of Stored Procedure -------------------------------
------------------------------- With in Stored Procedure -------------------------------
/* Start: Log variables */
DECLARE @cmd sysname, @var sysname
DECLARE @LogFilePath varchar(255)
/* End: Log variable */

SET @LogFilePath = 'D:\SPLog\log_file_name.log'
SET @var = 'Line you want to store to file/Message like: Start execution';
SET @cmd = 'echo '+@var+' >> '+@LogFilePath
exec master..xp_cmdshell @cmd

SET @var = 'Step 2 and so on...';
SET @cmd = 'echo '+@var+' >> '+@LogFilePath
exec master..xp_cmdshell @cmd
------------------------------- With in Stored Procedure -------------------------------

Introduction: Stored Procedure, Microsoft SQL Server

Stored Procedure Microsoft SQL Server

What is Stored Procedure?
Stored Procedure is nothing but the Program of database. In other words we can say its user defined function or method of database. That contains mixed database query like Insert, Select, Update, Delete, Join, Cursor, etc... in one file.

Why Stored Procedure?
Stored Procedure is very useful in case of complex project structure. Where you need to perform more than one operation on database. Write all you database queries in one SQL file, pass required parameter in it and rest will be on Stored Procedure.
Its not recommended to use Stored Procedure in case of one or two query execution.

Examples
Lets have hands on basic example of Stored Procedure. For better understanding and just to server basic idea. I'll use one query in my stored procedure however I won't recommend you to use it for one query.

1. Select all data of one organization (using organization id) and create new copy for new organization(using new organization id)
USE [NAME_OF_DATABASE]
GO
/*
 * Microsoft SQL Server
 * Stored Procedure : copyData.sql
 * Date of Creation : 24th July, 2013  
 * @author: Vicky Thakor
**/
/*
 * This option specifies the setting for ANSI NULL comparisons. 
 * When this is on, any query that compares a value with a null returns a 0. 
 * When off, any query that compares a value with a null returns a null value.
 **/
SET ANSI_NULLS ON
GO
/*
 * This options specifies the setting for usage of double quotation. 
 * When this is on, double quotation mark is used as part of the SQL Server identifier (object name). 
 * This can be useful in situations in which identifiers are also SQL Server reserved words.
 **/
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[copyData]
  -- Add the parameters for the stored procedure here
  @NewOrganizationID int,
  @OldOrganizationID int
AS
BEGIN/* PROCEDURE BEGIN */
 BEGIN TRANSACTION
        /* Stops the message that shows the count of the number of rows affected 
 * by a Transact-SQL statement or stored procedure from being returned as part of the result set. 
 * example : (1 row(s) affected)
 * Syntax : SET NOCOUNT { ON | OFF } 
 **/ 
        SET NOCOUNT ON;
        PRINT 'Copy data from Organization '+CAST(@OldOrganizationID AS VARCHAR(10))+' to '+CAST(@NewOrganizationID AS VARCHAR(10))
        /* Start: Copy rows */
        /* 
         * Below query find all rows where condition matches and insert new rows with new organizationID.
         * It doesn't matter how many rows are there.
         * To limit data you can use TOP n keyword after SELECT statement.
         */
        INSERT INTO [dbo].[TABLE_NAME]
               ([column_1] ,[column_2] ,[column_3] ,[ORGANIZATION_ID])
        SELECT  [column_1] ,[column_2] ,[column_3] ,@NewOrganizationID
        FROM TABLE_NAME
        WHERE ORGANIZATION_ID = @OldOrganizationID
        /* End: Copy rows */
        COMMIT TRANSACTION
END/* PROCEDURE END */
GO

2. Example of IF and WHILE for Microsoft SQL Server
USE [NAME_OF_DATABASE]
GO
/*
 * Microsoft SQL Server
 * Stored Procedure : Example2.sql
 * Date of Creation : 24th July, 2013  
 * @author: Vicky Thakor
**/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Example2]
  -- Add the parameters for the stored procedure here
  /* bit used for true or false in form of 1 and 0 */
  @PrintIFFlag bit,
  @LOOP_TO int
AS
BEGIN/* PROCEDURE BEGIN */
 BEGIN TRANSACTION
        /* Stops the message that shows the count of the number of rows affected 
 * by a Transact-SQL statement or stored procedure from being returned as part of the result set. 
 * example : (1 row(s) affected)
 * Syntax : SET NOCOUNT { ON | OFF } 
 **/ 
        SET NOCOUNT ON;
        DECLARE INDEX_COUNT = 0;

        IF @PrintIFFlag = 1
 BEGIN
  PRINT 'YOU ARE IN IF STATEMENT'
 END

        WHILE @INDEX_COUNT != @LOOP_TO
        BEGIN
              PRINT 'INDEX:'+CAST(@INDEX_COUNT AS VARCHAR(10))
              SET @INDEX_COUNT = INDEX_COUNT + 1;
        END         
        COMMIT TRANSACTION
END/* PROCEDURE END */
GO
-- EXEC Example2 1,100

How to execute Stored Procedure?
This Stored Procedure created for Microsoft SQL Server. Follow the below steps for complete execution.
  1. Execute Stored Procedure so for the first time it creates copy of stored procedure in database. For better understanding use Microsoft SQL Server Studio.
  2. After First execution change CREATE to ALTER because when you update something in your Stored Procedure you need to just alter the existing Stored Procedure. Database won't allow you to create Stored Procedure with same name.
  3. Fire EXEC NAME_OF_SP parameters. Check last line in example2

You have to be very careful while executing the stored procedure. As Stored Procedure perform lots of operation in database. You are recommended to follow some rules.
  • Before you create Stored Procedure on client's database test it on your local database.
  • While testing Stored Procedure Print required information using PRINT command. Like inserting, deleting, etc... 
  • If you are creating copy of information using some ID print previous ID and new ID. It'll help you to  delete the data from database. Stored Procedure will create garbage data on your database if you are not deleting test data.

Auto backup MySql database in windows with cloud

Its more important for developer and database admin to preserve data than codding. On a system crash you can code your software again but its hard to get bunch of data available in your database. My system crashed 2 times because of Hard disk error but thanks to cloud saving my ass. If you are new to cloud let me explain you what is cloud and importance of cloud.

Cloud Computing
Cloud Computing is an evolutionary milestone of Internet for your important data. Cloud sync your data from desktop, laptop, tablet, mobile to Internet and vise-versa. When you create file on a local system's cloud folder it'll upload it over Internet ASAP. So if your system crash the next morning you'll have all your important data available over internet and that may save your life.

Cloud Computing
Cloud Computing
Cloud Computing service provider
There are so many websites provides cloud computing for free with limited disk space. I listed some good website below. Create your account and folder on local computer.


Note: Don't create cloud folder on C: drive. Sometime only OS of system crash so it may wipe out your cloud folder on re-installation. If your cloud folder contains data of 1GB or more then it'll download all data again so better to just re-install cloud software and point it to old folder.

Creating backup of MySql to cloud folder

Step 1 : Download the javaQuery API [javaQuery Autobackup.bat] from http://www.javaquery.com/p/javaquery-api.html

Step 2 : Copy javaQuery Autobackup.bat in cloud folder say "cloud\database\autobackup program\". Edit file by right click on it > edit. Check out the code snippet of file.
:: MySql auto backup program by www.javaquery.com
:: Its a part of javaQueryAPI
:: Follow us on G+: https://plus.google.com/106033350352161272308 ; Facebook:https://www.facebook.com/javaquery.jq ; twitter:https://twitter.com/javaquery
:: This program wrote for those who work on local machine database. This will take backup with less options. If you are working with important database and want better options of backup use mysql workbench.

set year=%DATE:~10,4%
set day=%DATE:~7,2%
set mnt=%DATE:~4,2%
set hr=%TIME:~0,2%
set min=%TIME:~3,2%

IF %day% LSS 10 SET day=0%day:~1,1%
IF %mnt% LSS 10 SET mnt=0%mnt:~1,1%
IF %hr% LSS 10 SET hr=0%hr:~1,1%
IF %min% LSS 10 SET min=0%min:~1,1%

:: Don't use " in below path. Change file name as you want.
set fileName=javaQuery [Date~%day%-%mnt%-%year%;Time~%hr%-%min%].sql

:: settings for backup
:: Note: Don't use space between "=" and value; i.e : dbuser=root is correct; dbuser= root is wrong.

:: userName to login
set dbuser=root

:: password to login
set dbpass=root

:: schema to backup; If you want to backup all database use --database for dbschema
set dbschema=javaquery

:: set host ip address
set dbhost=127.0.0.1

:: set port of host
set dbport=3306

:: folderpath for backup. Don't use " in below path
set fldr=D:\Google Drive\Workspace\Database backup\

:: MySQLdump EXE Path
set mysqldumpexe="C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe"

%mysqldumpexe% --user=%dbuser% --password=%dbpass% --host=%dbhost% --port=%dbport% %dbschema% --routines --result-file="%fldr%%fileName%"

echo done...

::pause
:: If you find that backup file is not creating remove "::" from above line and it'll show you the execution of code.

Step 3 : You have to change the file in order to work on your local machine. I listed the data that you have to change in javaQuery Autobackup.bat file

  1. Change fileName ( Backup file name )
  2. Change dbuser ( Username to login database )
  3. Change dbpass ( Password to login database )
  4. Change dbschema ( Schema to create backup )
  5. Change dbhost ( ipAddress of host )
  6. Change dbport ( port of host )
  7. Change fldr ( Folder to store backup file )
  8. Change mysqldumpexe ( Executable file to create dump of mysql database )

Step up : If you want instant back up just run or double click the file. That will create the back up file at that time.

I created video to demonstrate how you can create the "Schedule Task" for backup.  

Getting started with MySQL and Java


                MySQL is an opensource Relational Database Management System. Now a days it is used by most of  website and software. Apart from that if you are Entrepreneur it helps to cut the cost. I personally use MySQL on my website. 

                Some of you have any miss guided news about MySQL here is your answer. Facebook uses MySQL. Read more about it on MySQL website : http://www.mysql.com/customers/view/?id=757

Facebook and MySQL statistics : 
  • Facebook process 60 million request in one second.
  • Facebook maintains database of more than 800 million users ; 500 million of them visits facebook daily
  • 350 million mobile user
  • 7 million application integrated with facebook platform.
  • Hope that's enough for you to understand what MySQL can handle...

Lets move to codding part quickly. Before you jump to any code download required software. Follow the links below to quick start : 

Database : ( MySQL )
  • MySQL Server
  • All of our support connectors
  • Workbench and sample models ( Provide database access using GUI )
  • MySQL Notifier
  • MySQL for Excel
  • MySQL for Visual Studio
  • Sample databases
  • Documentation

Library :

  • .jar file to connect database


Hope you have installed MySQL in your system. Now let me show you how you can connect to MySQL database with below code snippet.
import java.sql.*; // import SQL connection class
public final class MySQLDemo {
    public static final String dbhost = "jdbc:mysql://localhost:3306/javaQuery"; //javaQuery is schema name
    public static final String dbusername = "root";
    public static final String dbpassword = "root";
    public static final String dbdriver = "com.mysql.jdbc.Driver";

    public static void main(String[] args) {
        try {
            Class.forName(dbdriver); // Load driver
            Connection con = DriverManager.getConnection(dbhost, dbusername, dbpassword); // Create connection to database
            PreparedStatement selectData_stmt = con.prepareStatement("select * from DemoJavaQuery"); // Prepare query
            ResultSet selectData_rs = selectData_stmt.executeQuery(); // execute query and get data
            while(selectData_rs.next()){ //executes till last row available
                System.out.println(selectData_rs.getString("column_name")); // print data
            }
            /*
             * Close connection if you don't need further.
             * If you don't close it will create problem when you deal with large application
            */
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

For best practice read more on database:
http://www.javaquery.com/2013/02/preparedstatement-best-practice-of.html
http://www.javaquery.com/search/label/Database

PreparedStatement : Best practice of Database in Java


Database design

What is PreparedStatement?
Its an object that represents a precompiled SQL statement. It allows to execute the same or similar database statements repeatedly with high efficiency. Its template for your query that you use more than one time in your application.  

Why you need to use PreparedStatement over SQL statement?
  • SQL Statement : Create query model each time you fire the query in database. It will consume time to create model of your query. 
select * from user_table where(uid=1); / Creates model for query where uid=1
select * from user_table where(uid=2); / Creates model for query where uid=2
select * from user_table where(uid=1); / Creates model for query where uid=3
  • PreparedStatement : Create query model template on first time you fire the query in database. It'll use template already available in database. It don't need to create model when you change the uid parameter.
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo", "root", "root");
PreparedStatement userRecord_stmt = con.prepareStatement("select * from user_table where(uid=?);");
//Creates global model and used in future whether you use 1,2...n
userRecord_stmt.setInt(1,103);
ResultSet userRecord_rs = userRecord_stmt.executeQuery();
//next you know what to do...

Bind data in one query when bulk insert operation required.
Firing insert query each time when you need to insert 1000 or say more records its really time consuming. Best database practice is prepare one query that contains 1000 records in one query to insert. 
// Worst practice
insert into notification values(1,"hello world!"); // 1 request sent over network
insert into notification values(2,"hello world!"); // 1+1 request sent over network
insert into notification values(3,"hello world!"); // 1+1+1 request sent over network

// Best practice
insert into notification (a,b) values (1,"hell world!"),(2,"hell world!"),(3,"hell world!"); // Only 1 request sent over network

How to bind data in PreparedStatement?
Let me give you code snippet to perform bulk insert using PreparedStatement. 
PreparedStatement bulk_stmt = con.prepareStatement("insert into notification values(?,?);");
    for (int i = 0; i < 10; i++) {
      bulk_stmt.setInt(1, (i + 1));
      bulk_stmt.setString(2, "hello world!");
      bulk_stmt.addBatch();
    }
bulk_stmt.execute();

Find bulk data using "where column_name in( )"
Getting bulk information from table using single column comparison. 
  • Scenario : Find user details for the following uid : 1, 42, 33, 40, 103
// Worst practice 
select * from user_table where(uid=1);
select * from user_table where(uid=42);
select * from user_table where(uid=33);
// ... and so on

// Best practice
select * from user_table where uid in(1,42,33,40,103) ORDER BY uid;

How to get value of Auto Increment column value in java?

Database


We are creating so many application and we have to create it with proper way. If you creating application that uses Database. Then you have to keep in mind some basic rules before you go further.

  • Database design must be good - that help you to decrease the transaction count with database.
  • Use auto Increment column rather than using simple column for IDs.

Simple User:
  • Insert Column using (Database call)
  • select maximum id from table. (Database call)

Advance User:
  • Insert row and get return value of that inserted row.

Simple user uses typical logic of database but java support some feature that helps you lot. Below code give you the idea how Advance User saves time and less transaction.
Connection con = DriverManager.getConnection(Host, Username, Password);
Statement smt = con.createStatement();
boolean _generate = smt.execute("insert into test values(0);", smt.RETURN_GENERATED_KEYS);
ResultSet rs = smt.getGeneratedKeys();
if (rs.next()) {
 ID = rs.getInt(1);
}

How to connect database Dynamically in Java?

Hello friends I worked for J2EE and faced challenges regarding the database connection. We are working for java for long time but sometime we use complicated structure to do task. As we all know that to connect database we use the below code.
try {
     Class.forName("com.mysql.jdbc.Driver");
     Connection con = DriverManager.getConnection("jdbc:mysql://x.x.x.x:3306/db", "username", "password");
} catch (Exception e) { e.printStackTrace(); }

Problem: When we have more then 50 Java file which use the same database. We put the above code in all file but the major problem arise when we need to change database URL, Username, Password. We have to go through each and every file and sometime we forgot to change the URL, Username, Password in some code and we will stuck with error. To come over this problem I did the simple logic which you all know but didn't came in your mind. So here I'm sharing the idea.

Step 1: Create bean file.
/* dbconnect.java */
public class dbconnect {
    private String _host = "jdbc:mysql://localhost:3306/db";
    private String username = "root";
    private String password = "root";
  
    public String getHost() {
        return _host;
    }
 
    public String getUsername() {
        return username;
    }
 
    public String getPassword() {
        return password;
    }
}
Step 2: Import above file in file where you want to use database.
/* getDetailsTest.java */
public class getDetailsTest {
    public static void main(String[] args) {
        dbconnect _db = new dbconnect();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection(_db.getHost(), _db.getUsername(), _db.getPassword());
        } catch (Exception e) { e.printStackTrace(); }
    }
}
Now when you change the Host, Username, Password in dbconnect.java file. Now getDetailsTest.java file use new URL, Username, Password. No need to change 50 file for database connection. Just change one file.

How to get value from database without refreshing JSP page?

There are situations when you need data without refreshing current page. We can achieve this using jQuery or AJAX. Following example demonstrate it using jQuery.

test.html
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <script src="https://code.jquery.com/jquery-1.10.2.js"></script>
        <script>
            $(document).ready(function(){
                 $("#users").change(function(){
                     var value = $(this).val();
                     $.get("data.jsp",{q:value},function(data){
                      $("#javaquery").html(data);
                     });
                 });
             });
        </script>
    </head>
    <body>
        <select id = "users">
           <option value="">Select Account ID</option>
           <option value="0">0</option>
           <option value="1">1</option>
        </select>
        <br />
        <div id="javaquery"><b>Name will be displayed here</b></div>
    </body>
</html>

data.jsp
<%
   String name = "";
   String q = request.getParameter("q");
   try {
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://x.x.x.x:3306/javaquery", "username", "password");
        Statement smt = con.createStatement(); //Create Statement to interact
        ResultSet r = smt.executeQuery("select * from users where(AccountID='" + q + "');");
        while (r.next()) {
          name = r.getString("name");
        }
        con.close
   } catch (Exception e) {
        e.printStackTrace();
   }
%>
Name:<%out.print(name);%>
Output:
jquery, ajax

jquery, ajax

How to Import Export JavaDB(Database) in Netbeans?

JavaDB
Java DB is Oracle's supported distribution of the Apache Derby open source database. It supports standard ANSI/ISO SQL through the JDBC and Java EE APIs. Java DB is included in the JDK.

  • - Full-featured and easy-to-use
  • - Transaction-protected and crash-recoverable
  • - Embeddable in applications
  • - Pure Java and portable and across CDC FP 1.1, Java 5, Java 6, and Java 7 (everywhere from tablets to mainframes)


How to create JavaDB in Netbeans?

Step 1: Open service explorer in netbeans and navigate to Database > JavaDB > Create Database

JavaDB, Netbeans

Step 2: Provide Database Name, Username, Password and Database location in next popup. You are done with database creation in JavaDB. 


How to insert value in JavaDB in Netbeans?
If you are creating database for the first time in netbeans by following above steps. Then it'll show you the database instance right away. If you want to import it from other location then continue reading article till the end. I'm going to show you how you can import JavaDB in Netbeans.

Step 1: Connect the database instance that you created in above steps.

JavaDB, Netbeans

Step 2: You can create new schema in it. Or simple you can use APP schema. Open APP schema hierarchy > Table > Create Table

JavaDB, Netbeans

Step 3: Create table in next popup. 

Step 4: Right click on table and Insert record in it or by program. 


How to import JavaDB in Netbeans?

Step 1: Right click on JavaDB > Properties and change database location.

JavaDB, Netbeans

Step 2: Create New Connection.

JavaDB, Netbeans

Step 3: Provide database credential in next popup and click on ok.

JavaDB, Netbeans

How to Run JavaDB without using Netbeans?

JDBC, Database
JavaDB is not preferred for big application. However you can use if for college projects. Let me show you how you can connect with JavaDB directly from your windows application(.jar).

I'm considering that you created database using some IDE like netbeans or other. Download database with all required file, can be moved in any system directly. http://db.apache.org/derby/derby_downloads.html

Step 1: Download database and copy folder(extracted zip) in your application folder.

Step 2: Copy your database folder in database_path\JavaDB\bin .

JDBC

HZL is my database folder contain database file. 
Note: HZL is my root folder. Don't create new folder in "bin" paste the folder direct. Below figure show contains of HZL folder.

JDBC

Step 3: Start database server. Open database_path\JavaDB\bin\startnetworkserver.bat file. That will start your database server. 

JDBC
Don't close this command prompt. 

Step 4: Stop Server
  • - By Pressing cltr+C
  • - JavaDB\bin\Stopnetworkserver.bat


You might interested in start database at the start of application by programming.
Execute startnetworkserver.bat using java program. Read further about executing command using java program. http://www.javaquery.com/2011/03/how-to-execute-multiple-command-in.html

[Updated: 01/06/2013] How to update jtable dynamically from Oracle , MySQL (Database)?

Once i found updating jTable dynamically was tough task. Now I'll help you to understand that code. I'm considering that you are working in Integrated Development Environment. Don't waste your time by coding your project in text editor like notepad or so.

jTable
jTable

I'm considering that you already declared jTable in your code. I'm updating the jTable data on formWindowOpened. You can set your own event for the code below.

// import database class as per oracle and mysql selection.
private void formWindowOpened(java.awt.event.WindowEvent evt) {
        Object[][] rowData = new Object[2][2];
        Object columnNames[] = {"Client ID", "Client Name"};
        int i = 0;
        try {
            /*
             * It's my own function to load database connection.
             * You have to load database driver and connection.
             * public static final Connection getDatabaseConnection() throws Exception {
             * Class.forName(dbdriver);
             * return DriverManager.getConnection(dbhost, dbusername, dbpassword);
             * }
             */
            Connection con = variables.getDatabaseConnection(); 
            PreparedStatement client_stmt = con.prepareStatement("select idclients,ClientName from clients limit 0,2");
            ResultSet client_rs = client_stmt.executeQuery();
            while (client_rs.next()) {
                rowData[i][0] = client_rs.getString("idclients");
                rowData[i][1] = client_rs.getString("ClientName");
                i++;
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        DefaultTableModel tm = new DefaultTableModel(rowData, columnNames);
        jTable1.setModel(tm);       
}

This is it. Choose your event to load the data. Don't forget to change the database query and its column name. Subscribe to Google plus and Facebook for more stuffs...

How to use Oracle Database in Java Program?

Oracle

Access Oracle Database using the Java program is very easy task. All you need is basic database knowledge. Before you try to connect let me give you quick list that you need for your program.


try {
  //Driver to Connect with Database
  Class.forName("oracle.jdbc.OracleDriver"); 
} catch (Exception ex) {
  ex.printStackTrace();
}
try {
  String DBlink = "jdbc:oracle:thin:@vicky-pc:1521:test"; 
  //vicky-pc: Hostname / 1521: port / test: Databasename
  Connection con = DriverManager.getConnection(DBlink, "username", "password");
  Statement smt = con.createStatement();
  ResultSet rs = smt.executeQuery("select * from tab");
  // rs contains all return values for your query
  while(rs.next()){
   String data = rs.getString("Column_Name");
  }
  con.close();
} catch (Exception e) {
  e.printStackTrace();
}

This is simple way to connect to database. Change your connection and table credential in above program.

You might wanna check best practice for database operation(s) in following article: PreparedStatement : Best practice of Database in Java