将 JAVA 应用程序连接到 SQL SERVER

Connecting JAVA App to SQL SERVER

提问by Crystal Maiden

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.

我正在尝试将 JAVA 连接到 SQL SERVER，但我不知道该怎么做，上学期我们制作了一个使用 MS ACCESS 作为数据库的程序，我想知道我是否也可以使用它来连接我正在为 SQL SERVER 创建的程序。

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

这是我在 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?

我可以用这个吗？

回答by Shahriar N Khondokar

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

要从 Java 应用程序连接到 MS SQL Server，您需要使用JDBC API. 在JDBC API提供连接到数据库的类和方法，加载相应的驱动程序，发送SQL查询，检索结果等。

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:

如何连接到数据库：“ Connection”对象表示与数据库的连接。要建立连接，请使用方法“ DriverManager.getConnection”。此方法采用包含 URL 的字符串，该 URL 表示我们尝试连接的数据库。下面是建立连接的示例代码：

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.

关于数据库 URL 以及如何创建它的详细讨论可以在本文末尾提供的资源中找到。

QUERYING THE DATABASE:The JDBC APIprovides 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.prepareStatementmethods'. A ‘PreparedStatement' object is used for precompiled SQL statements. These can take one or more parameters as input arguments (INparameters). 3.CallableStatement- created by the ‘Connection.prepareCall' methods. ‘CallableStatement' objects are used to execute SQL stored procedures from Java database applications.

查询数据库：将JDBC API提供SQL语句发送到数据库三个接口，以及相应的方法在“Connection他们的”接口创建实例。 1.Statement- 由 ' Connection.createStatement' 方法创建。' Statement' 对象用于发送不带参数的 SQL 语句。 2.PreparedStatement- 由“Connection.prepareStatement方法”创建。' PreparedStatement' 对象用于预编译的 SQL 语句。这些可以将一个或多个参数作为输入参数（INparameters）。 3.CallableStatement- 由 ' Connection.prepareCall' 方法创建。' CallableStatement' 对象用于从 Java 数据库应用程序执行 SQL 存储过程。

RETRIEVING THE RESULT:A ‘ResultSetis 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':

检索结果：'ResultSet是一个 Java 对象，它包含执行 SQL 查询的结果。存储在“ ResultSet”对象中的数据是通过一组允许访问当前行的各个列的 get 方法检索的。' ResultSet.next' 方法用于移动到 ' ResultSet'的下一行，使其成为当前行。以下代码片段执行返回行集合的查询，列“ a”作为“ int”，列“ b”作为“ String”，列“ c”作为“ 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.

这只是关于如何从 Java 与数据库交互的简要介绍。有关上述项目的更多详细信息以及有关传递参数、执行存储过程等的信息，请参阅以下资源：（http://www.shahriarnk.com/embedding-sql-c-sharp-java-shahriar /#Shahriar_N_Embedding_SQL_in_Java) 在这里，您还将找到有关如何以编程方式与数据库交互的信息；即不使用 SQL。希望您觉得这个有帮助。

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

资料来源：www.shahriarnk.com/embedding-sql-c-sharp-java-shahriar/

回答by Gander7

Every DB(Database) that you connect to will have a different connection string, and you will have a different Class depending on the DB you are using. This is the one you are using in the above example:

您连接到的每个 DB（数据库）都将具有不同的连接字符串，并且根据您使用的 DB，您将拥有不同的类。这是您在上面的示例中使用的：

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

You need to get the connection String for the SQL Server and the driver. Both are easily researched.

您需要获取 SQL Server 和驱动程序的连接字符串。两者都很容易研究。

If you are using eclipse, netbeans or most IDEs for ease they will have a server explorer window which you can add a connection to and will give you easy access to your connection string.

如果您正在使用 eclipse、netbeans 或大多数 IDE，它们将有一个服务器资源管理器窗口，您可以在其中添加连接，并让您轻松访问连接字符串。