Connecting JAVA App to SQL SERVER

Question

I'm trying to connect JAVA to SQL SERVER but I don't have any idea how to do it, last semester we make a program that is using MS ACCESS as it's DATABASE, I was wondering if I can used it too to connect the program that I'm creating to SQL SERVER.

Here is the code that I used in Java.Main:

package pkg3a3pgroupsix;

import java.sql.*;
import javax.swing.*;
public class Main {

Connection conn = null;
ResultSet rs = null;
PreparedStatement pst = null;
Statement s;

    public static Connection ConnectDatabase(){

    try{
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Connection conn = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)}; DBQ=BillingSystem.mdb");

        return conn;
    }catch(Exception e){
        JOptionPane.showMessageDialog(null, e);
        return null;

    }
}

    public static void main(String[] args) {
         try {
            for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) {
                if ("Windows".equals(info.getName())) {
                    javax.swing.UIManager.setLookAndFeel(info.getClassName());
                    break;
                }
            }
        } catch (ClassNotFoundException ex) {
            java.util.logging.Logger.getLogger(Login.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            java.util.logging.Logger.getLogger(Login.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            java.util.logging.Logger.getLogger(Login.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (javax.swing.UnsupportedLookAndFeelException ex) {
            java.util.logging.Logger.getLogger(Login.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        }
        // TODO code application logic here
    }
}

and here is the code that I used to make a Login on it:

package pkg3a3pgroupsix;

import java.awt.*;
import java.nio.channels.SeekableByteChannel;
import javax.swing.*;
import java.sql.*;
public class Login extends javax.swing.JFrame {
Connection conn;
ResultSet rs = null;
PreparedStatement pst = null;
int counter = 0;

public void registration(){
    User_Registration ur = new User_Registration();
    ur.setVisible(true);
    ur.setExtendedState(MAXIMIZED_BOTH);
    this.dispose();
}
public void exit (){
    int selectedOption = JOptionPane.showConfirmDialog(null, 
                                  "Do you wanna close the window?", 
                                  "Exit", 
                                  JOptionPane.YES_NO_OPTION); 
        if (selectedOption == JOptionPane.YES_OPTION) { this.dispose();}
}

    public Login() {
        initComponents();
    }                      

    private void btnloginActionPerformed(java.awt.event.ActionEvent evt) {                                         
            // TODO add your handling code here:
        String sqllogin = "select * from Employee where Username='" + jTextField1.getText() + "' and Password='" + jPasswordField1.getText() + "'";
        try {
            pst = conn.prepareStatement(sqllogin);
            rs = pst.executeQuery();
            if (rs.next()) {
            SelectionScreen s = new SelectionScreen();
            s.setVisible(true);
            this.dispose();counter = 0;}
            else {JOptionPane.showMessageDialog(null, "Invalid Username or Password!");counter += 1;
            if (counter == 1) {JOptionPane.showMessageDialog(null, "WARNING! 1st Attempt of Log-In!");} 
            else if (counter == 2) {JOptionPane.showMessageDialog(null, "WARNING! 2nd Attempt of Log-In!");}}
            if (counter == 3) {JOptionPane.showMessageDialog(null, "WARNING! 3rd Attempt of Log-In!");
            {JOptionPane.showMessageDialog(null, "The Program Will Shutdown.");this.dispose();}}}
        catch (Exception e) {JOptionPane.showMessageDialog(null, e);}
    }                                        

    private void formWindowOpened(java.awt.event.WindowEvent evt) {                                  

        conn = Main.ConnectDatabase();
    }                                 

    private void btnexitActionPerformed(java.awt.event.ActionEvent evt) {                                        

        exit();
    }                                       

    private void btnAdminActionPerformed(java.awt.event.ActionEvent evt) {                                         

        String sqllogin = "select * from Login where Username='" + jTextField1.getText() + "' and Password='" + jPasswordField1.getText() + "'";
        try {
            pst = conn.prepareStatement(sqllogin);
            rs = pst.executeQuery();
            if (rs.next()) {
            User_Registration UR = new User_Registration();
            UR.setVisible(true);
            this.dispose();counter = 0;}
            else {JOptionPane.showMessageDialog(null, "Invalid Username or Password!");counter += 1;
            if (counter == 1) {JOptionPane.showMessageDialog(null, "WARNING! 1st Attempt of Log-In!");} 
            else if (counter == 2) {JOptionPane.showMessageDialog(null, "WARNING! 2nd Attempt of Log-In!");}}
            if (counter == 3) {JOptionPane.showMessageDialog(null, "WARNING! 3rd Attempt of Log-In!");
            {JOptionPane.showMessageDialog(null, "The Program Will Shutdown.");this.dispose();}}}
        catch (Exception e) {JOptionPane.showMessageDialog(null, e);}
    }  

CAN I USED THIS?

Solution

To connect to MS SQL Server from a Java application, you need to use the JDBC API. The JDBC API provides classes and methods that connect to the database, load the appropriate driver, send SQL queries, retrieve results etc.

HOW TO CONNECT TO THE DATABASE: A ‘Connection’ object represents a connection with a database. To establish the connection, use the method ‘DriverManager.getConnection’. This method takes a string containing a URL which represents the database we are trying to connect to. Below is the sample code for establishing a connection:

private String DATABASE_URL = "jdbc:odbc:embedded_sql_app"; // establish connection to database
Connection connection = DriverManager.getConnection( DATABASE_URL,"sa","123" );

Detailed discussion about the Database URL and how to create it can be found in the resource provided at the end of this post.

QUERYING THE DATABASE: The JDBC API provides three interfaces for sending SQL statements to the database, and corresponding methods in the ‘Connection’ interface create instances of them. 1. Statement - created by the ‘Connection.createStatement’ methods. A ‘Statement’ object is used for sending SQL statements with no parameters. 2. PreparedStatement - created by the ‘Connection.prepareStatement methods’. A ‘PreparedStatement’ object is used for precompiled SQL statements. These can take one or more parameters as input arguments (IN parameters). 3. CallableStatement - created by the ‘Connection.prepareCall’ methods. ‘CallableStatement’ objects are used to execute SQL stored procedures from Java database applications.

RETRIEVING THE RESULT: A ‘ResultSet is a Java object that contains the results of executing a SQL query. The data stored in a ‘ResultSet’ object is retrieved through a set of get methods that allows access to the various columns of the current row. The ‘ResultSet.next’ method is used to move to the next row of the ‘ResultSet’, making it the current row. The following code fragment executes a query that returns a collection of rows, with column ‘a’ as an ‘int’, column ‘b’ as a ‘String’, and column ‘c’ as a ‘float’:

 java.sql.Statement stmt = con.createStatement();
 ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1");
 while (rs.next()) { // retrieve and print the values for the current row
     int i = rs.getInt("a");
     String s = rs.getString("b");
     float f = rs.getFloat("c");
     System.out.println("ROW = " + i + " " + s + " " + f);
 }

This is just a brief introduction on how to interact with a database from Java. For more details on the items discussed above as well as information on passing parameters, executing stored procedures etc. please refer to the following resource: ( http://www.shahriarnk.com/embedding-sql-c-sharp-java-shahriar/#Shahriar_N_Embedding_SQL_in_Java ) Here, you will also find information on how to interact with a database programmatically; i.e. without using SQL. Hope you find this useful.

Source: www.shahriarnk.com/embedding-sql-c-sharp-java-shahriar/