Java methods for successful access to SQL Server 2000 databases

server|sql| Access | data | database

Absrtact: This article mainly introduces the use of JDBC, ODBC interface to achieve the connection with SQL Server2000 database, and use Java application to access it, at the same time through the graphical user interface to achieve a simple query function.

Objective

Database technology and network technology are two hot topics in the field of computer science, and the database technology has been developed in technology since its generation. And as the front-end access to development tools and environment is still in the continuous improvement and development, in addition to the use of the network ASP, PHP, JSP as the front-end connection database technology, small systems commonly used Access database technology Delphi, Visual Basic, PowerBuilder and vc+ + And so on, and Java is one of them.

The Java language is one of the outstanding languages for writing database applications, and it provides the technology to facilitate access to data. Using JDBC technology in Java language, users can easily develop a database access program based on Web page, thus expanding the network application function. JDBC (Java DB Connectivity,java database connection) is a Java API for executing SQL statements that provides a unified access interface for a variety of relational databases. JDBC consists of a set of classes and interfaces written in the Java language that enable users to connect to many different database systems in a consistent way (such as Access, SQL Server 2000, Oracle, Sybase, and so on) by invoking the methods provided by these classes and interfaces. You can then use the standard SQL language to access the data in the database without having to write different Java program code for each database system.

SQL Server2000 is a new generation of Microsoft database products, it is built on the solid foundation of SQL Server7.0, and a large number of SQL Server7.0 expansion. SQL Server2000 delivers scalability and high reliability for the largest Web sites and enterprise-class applications with the support of high-end hardware platforms and the latest network and storage technologies.

Java Access database via JDBC, ODBC interface

1 JDBC, ODBC interface

The Java application connects to the database through the JDBC API (java.sql), and the actual action is connected to the database system by the JDBC Driver Manager (JDBC Driver manager) through the JDBC driver. ODBC (Open DataBase connectivity), an open interface, provides users with a standard interface for accessing relational databases, which provides a unified set of APIs for different databases ; You can enable applications to access any database that provides ODBC drivers through the API, and all relational databases currently provide ODBC drivers, so ODBC has become the industry standard for database access and has been widely used.

The Jdbc-odbc Bridge is a JDBC driver that is implemented by converting JDBC operations into ODBC operations. The use of the Jdbc-odbc Bridge allows program developers to write JDBC applications without learning more knowledge and to take full advantage of existing ODBC data sources. The JDBC-ODBC bridge driver can enable JDBC to access almost all types of databases.

2 Accessing SQL Server2000 databases with Java applications

(1) Establishing a database

Start Microsoft SQL Server2000, open Enterprise Manager to create a database named "MyData" in database, and make a datasheet named "Wuzi" below it, as shown in Figure 1.

Figure 1

(2) establishing (ODBC) data sources and drivers

Open the ODBC Data Source Administrator dialog box on the control panel through the data Source (ODBC) of Administrative Tools. Click the System DSN tab, and then click the Add button to get the Create Data Source dialog box, select SQL Server and click Finish to the Create a new data source to the data source name item in the SQL Server dialog box fill in "Wzgl" and choose "Server Name". Then click the Next button, select the Windows NT Authentication with network logon ID project, click Next, and change the default database to MyData, click Next, click Finish, and then click Test Data Source, and then click OK to complete the (ODBC) data source and driver setup.

3 Writing code

To save the code in the Jdbc.java file:

Import Java.awt.*;import Java.awt.event.*;import java.sql.*;

public class JDBC//Definition main class

{

public static void Main (String args[])

{

GUI Gui=new gui (); To create an object of the class GUI

Gui.pack (); Load execution GUI class

}

}

Class GUI extends Frame implements Action Listener

{

TextArea text; Panel panel; TextField Sno; Button btn;

GUI ()//construction method

{

Super ("Material situation Inquiry"); setlayout (new BorderLayout ());

SetBackground (Color.cyan);

SetVisible (true); text=new TextArea ();

Btn=new button ("Query");

Sno=new TextField (16);

Panel=new Panel ();

Panel.add ("Enter the item number being queried:");

Panel.add (SNO); Panel.add (BTN);

Add ("North", panel); Add (Text, "Center");

Text.seteditable (false); Btn.addactionlistener (this);

Addwindowlistener (New Windowadapter ()

{

public void windowclosing (WindowEvent e)

{

SetVisible (FALSE);

System.exit (0);

}

});

}

public void actionperformed (ActionEvent e) {

if (E.getsource () ==btn)////When the user presses the query button

{

Text.settext ("Query result" + ' \ n '); Show Tips

Try

{

Liststudent ();

}

catch (SQLException ee) {}

}

}

public void Liststudent () throws SQLException//for database operations

{

String BH,MC,XH,LB,DW,SJ;

int SL; float Dj,je;

Try

{

Class.forName ("Sun.jdbc.odbc.JdbcOdbcDriver");

}

catch (ClassNotFoundException e) {}

Connection con=drivermanager.getconnection ("Jdbc:odbc:wzgl");

Statement sql=con.createstatement (); Creating statement objects

ResultSet rs=sql.executequery ("SELECT * from Wuzi");

while (Rs.next ())//output is queried

{

Bh=rs.getstring ("material number");

Mc=rs.getstring ("Material name");

Xh=rs.getstring ("specification model");

Lb=rs.getstring ("category");

Dw=rs.getstring ("Unit of Measure");

Sl=rs.getint ("Quantity");

Dj=rs.getfloat ("Unit Price");

Je=rs.getfloat ("Amount");

Sj=rs.getdate ("Time"). ToString ();

if (Bh.trim (). Equals (Sno.gettext (). Trim ())

{

Text.append (' \ n ' + "material number" + "" + "material name" + "" + "specification" + "" + "category" + "" + ") Units of measurement" + "" + "Quantity" + "" + "unit Price" + "" + "Amount" + "" + "time" + ' \ n ');

Text.append (' \ n ' +bh+ "" +mc+ "" +xh+ "" +lb+ "" +dw+ "" +sl+ "" +dj+ "" +je+ "" +sj+ "" + ' \ n ') ";

}

}

}

}

4 Running the program

First compilation: Javac Jdbc.java

After compiling successfully, execute: Java JDBC

After execution, enter the item number in the text box that you want to inquire about, click the "Query" button, and in the text box below, display all the information about the items being queried. As shown in Figure 2.

Figure 2

Conclusion

This article only involves the query operation to the SQL Server2000 database access, but also can insert, delete, modify and so on operation. With the rapid development of database technology and the continuous upgrading of the Java version, the use of Java access to the database will be more and more simple, and the scope of application will be more and more wide, of course, there will be many new features waiting for us to develop.