Using the database (SQL Server 2014) from a Java program 2018/01/04

Connect the java program and DB (SQL Server 2014)

environment

• java 8
• eclipse
• SQL server 2014

What you need to use DB from java program

① java.sql package --- Import and use. No special preparation required. ② JDBC driver --- java DataBase Connector, library. It consists of a single jar file that contains interfaces and classes for managing the database. In this case, it will be used in Eclipse's dynamic web project, so place it under WEB-INF / lib (= it will be automatically added to the classpath).

Install the JDBC driver

--Install the JDBC driver for SQL server (choose the Japanese version). https://www.microsoft.com/en-us/download/details.aspx?id=55539

-Placed under / (package name) /WebContent/WEB-INF/lib (added this time is mssql-jdbc-6.2.2.jre8.jar).

Write a program for SQL connection

http://www.codejava.net/java-se/jdbc/connect-to-microsoft-sql-server-via-jdbc While referring to When I try to run a program that logs in with Windows authentication `com.microsoft.sqlserver.jdbc.SQLServerException: User'' was unable to log in. `` I got an error, so when I tried to log in with SQL authentication, the connection was successful with the following code.

package (package name);

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Practicing {

	//Check if the driver is loaded
	// public static void main(String[] args) throws InstantiationException,
	// IllegalAccessException {
	// String msg = "";
	// try {
	// Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
	// msg = "The driver was successfully loaded";
	// } catch (ClassNotFoundException e){
	// msg = "Failed to load the driver";
	// }
	// System.out.println(msg);
	// }

	public static void main(String[] args) {

		Connection conn = null;

		try {

			String dbURL = "jdbc:sqlserver://localhost\\sqlexpress";
			String user = "sa";
			String pass = "*****(Password set during SQL server installation)";
			conn = DriverManager.getConnection(dbURL, user, pass);

			if (conn != null) {
				DatabaseMetaData dm = (DatabaseMetaData) conn.getMetaData();
				System.out.println("Driver name: " + dm.getDriverName());
				System.out.println("Driver version: " + dm.getDriverVersion());
				System.out.println("Product name: " + dm.getDatabaseProductName());
				System.out.println("Product version: " + dm.getDatabaseProductVersion());
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					conn.close();
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
	}
}

Execution result:

Postscript: When I run this program one day, I get the following error.

com.microsoft.sqlserver.jdbc.SQLServerException: The connection to the host localhost, named instance sqlexpress failed. Error: "java.net.SocketTimeoutException: Receive timed out". Verify the server and instance names and check that no firewall is blocking UDP traffic to port 1434. For SQL Server 2005 or later, verify that the SQL Server Browser Service is running on the host. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:227) at com.microsoft.sqlserver.jdbc.SQLServerConnection.getInstancePort(SQLServerConnection.java:5241) at com.microsoft.sqlserver.jdbc.SQLServerConnection.primaryPermissionCheck(SQLServerConnection.java:1916) at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1669) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1528) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:866) at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:569) at java.sql.DriverManager.getConnection(DriverManager.java:664) at java.sql.DriverManager.getConnection(DriverManager.java:247) at chkJDBC.ListPracticing.main(ListPracticing.java:34)

If you are using "For SQL Server 2005 or later, verify that the SQL Server Browser Service is running on the host" in this message, SQL Server 2005 or later, SQL Server Browser is running. Please confirm that.

Make sure that the service is running both SQL Server (instance name, SQL EXPRESS in this case) and SQL Server Browser. This time the Server Browser wasn't running. Start> Services> Applicable 2 below, right click if it is not "Running", "Start"

When I ran the program again, it ran successfully!