Home > Developer > SQL Server
"JDBC Connection to SQL Server"
"Ready to Work"
1, download Eclipse-javaee;
2, download Tomcat8.0;
3. Download Microsoft Jdbcdriver 4.1 for SQL Server;
4, copy the \sqljdbc_4.1\chs\sqljdbc41.jar to the Tomcat8.0 installation directory in the Lib folder;
5. Windows->preference->server->runtimeenvironment->add->apache Tomcat in Eclipse v8.0, select the Tomcat installation directory in the Tomcatinstallation library path;
5, create Dynamic Web project: File->new->other->web->dynamic Web project, project name: Test;
6, in the test project to create a JSP file newfile.jsp;
7, file->new-> create server-> will test project join-to complete
The above steps complete the creation of the JSP project ***************
"Statement Introduction"
1. Load the JDBC Driver
Class.forName ("Com.microsoft.sqlserver.jdbc.SQLServerDriver");
2. Create a connection to the database
Connection con = drivermanager.getconnection (Url,user,password);
The connection URL defines the protocol, sub-protocol, and data source identity when the database is connected.
Writing form: Protocol: Sub-Protocol: Data source identification
Protocol: Always start with JDBC in JDBC
Sub-Protocol: A bridge-connected driver or database management system name.
Data source identification: The tag locates the address of the database source and the connection port.
String url= "Jdbc:sqlserver://localhost:1433;databasename=educ"
String user= "SA"
String password= "SA"
3. Create a statement
Statementstmt=con.createstatement ();
To execute the SQL statement, you must obtain the Java.sql.Statement instance, which is divided into the following 3 types of statement instances:
1) Execute static SQL statements. Typically implemented through statement instances.
2) Execute dynamic SQL statements. Typically implemented through PreparedStatement instances.
3) Execute the database stored procedure. Typically implemented through CallableStatement instances.
4. Query statements
ResultSet rst=stmt.executequery ("select* from Student");
while (Rst.next ())
{
Out.println ("<tr>");
Out.println ("<td>" +rst.getstring ("sno") + "</td>");
Out.println ("<td>" +rst.getstring ("sname") + "</td>");
Out.println ("<td>" +rst.getstring ("sage") + "</td>");
Out.println ("</tr>");
}
5. Insert statement
String ins= "Insert intostudent values (5, ' Luoyang ', 21);";
Stmt.executeupdate (INS);
6. Delete statements
String del= "Delete from student whereid=5;";
Stmt.executeupdate (DEL);
7, the table creation Update DELETE statement is similar to the above operation method, no longer repeat.
8. Close the JDBC Object
Rst.close ();
Stmt.close ();
Con.close ();
"Execution Results"
"Source Code" (Description: Data insertions and deletions are shown in the "JDBC Connection MySQL" code)
<% @pageContentType= "text/html; charset=gb2312"language= "Java"Import= "java.sql.*"%><HTML><Body>The following is the data read from the SQL Server database:<HR><Tableborder=1><tr><TD>Sno</TD><TD>Sname</TD><TD>Sage</TD></TR> <% Class.forName("Com.microsoft.sqlserver.jdbc.SQLServerDriver"); Connection Con= Drivermanager.getconnection ("Jdbc:sqlserver://localhost:1433;databasename=educ", "sa", "sa"); Statement stmt=con.createstatement (); ResultSet rst=stmt.executequery ("select*From student "); while (Rst.next ()) {out.println ("<tr>"); Out.println ("<TD>"+rst.getstring (" sno ") +"</TD>"); Out.println ("<TD>"+rst.getstring (" sname ") +"</TD>"); Out.println ("<TD>"+rst.getstring (" sage ") +"</TD>"); Out.println ("</TR>"); }//close connection, release resource Rst.close (); Stmt.close (); Con.close ();%></Table></Body></HTML>
"JDBC Connection MySQL"
Methods and procedures are basically similar to connecting to SQL Server.
The syntax is slightly different when loading the JDBC driver and connection to create a database connection, and you need to be careful.
Class.forName ("Com.mysql.jdbc.Driver");
Connection con = drivermanager.getconnection ("Jdbc:mysql://localhost:3306/school", "root", "ly941122");
"Run Results"
"Source Code"
<% @pageContentType= "text/html; charset=gb2312"language= "Java"Import= "java.sql.*"%><HTML><Body>The following is the data read from the MySQL database:<HR><Tableborder=1><tr><TD>Id</TD><TD>Name</TD><TD>Age</TD></TR> <% Class.forName("Com.mysql.jdbc.Driver"); Connectioncon=drivermanager.getconnection ("Jdbc:mysql://localhost:3306/school", "root", "ly941122"); Statement stmt=con.createstatement (); //string ins= "INSERT into student values (5, ' Luoyang ', +);"; String del= "Delete from student whereid=5;"; Stmt.executeupdate (INS); Stmt.executeupdate (DEL); ResultSet rst=stmt.executequery ("select*From student "); while (Rst.next ()) {out.println ("<tr>"); Out.println ("<TD>"+rst.getstring (" id ") +"</TD>"); Out.println ("<TD>"+rst.getstring (" name ") +"</TD>"); Out.println ("<TD>"+rst.getstring (" age ") +"</TD>"); Out.println ("</TR>"); }//close connection, release resource Rst.close (); Stmt.close (); Con.close ();%></Table></Body></HTML>
JDBC Connection database (SQL Server and MySQL) configuration summary
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
