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

Connect the java program and DB (SQL Server 2014)

environment

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).

JDBCドライバ配置場所.png

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: 実行結果.png

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" SQLwhenError.png

When I ran the program again, it ran successfully!

Recommended Posts

Using the database (SQL Server 2014) from a Java program 2018/01/04
[Java] Generate a narrowed list from multiple lists using the Stream API
Try accessing the dataset from Java using JZOS
Ssh connect using SSHJ from a Java 6 app
Creating a sample program using the problem of a database specialist in DDD Improvement 2
Place Java project using database on the server created in VPS so far
Creating a sample program using the problem of a database specialist in DDD Improvement 1
[Java] [SQL Server] Connect to local SQL Server 2017 using JDBC for SQL Server
3. Create a database to access from the web module
How to jump from Eclipse Java to a SQL file
I tried using Log4j2 on a Java EE server
Call a program written in Swift from Processing (Java)
Using Docker from Java Gradle
Make a rhombus using Java
I tried using the GitHub repository as a library server
JSON in Java and Jackson Part 1 Return JSON from the server
[JDBC] I tried to access the SQLite3 database from Java.
Try launching a webAP server on the micro using Helidon
Create a MOB using the Minecraft Java Mythicmobs plugin | Preparation 1
Create a simple web server with the Java standard library com.sun.net.httpserver
Upload a file using Java HttpURLConnection
Run a batch file from Java
[Java ~ Creating a self-introduction program while reviewing the past) ~] Study memo (7)
Connecting to a database with Java (Part 1) Maybe the basic method
Access Teradata from a Java application
Access the in-memory data grid Apache Ignite from a Java client
AWS Elastic Beanstalk # 1 with Java starting from scratch-Building a Java web application environment using the EB CLI-
The road from JavaScript to Java
Sample code using Minio from Java
[Gradle] Build a Java project with a configuration different from the convention
[JDBC] I tried to make SQLite3 database access from Java into a method for each SQL statement.
I tried to make a sample program using the problem of database specialist in Domain Driven Design
How to return a value from Model to Controller using the [Swift5] protocol
[Java] How to retrieve the parameters passed from html on the server side
Verify the ID token obtained from Firebase on the server side (Java + SpringBoot)
[Java] How to get to the front of a specific string using the String class
Sample program that returns the hash value of a file in Java
Data processing using stream API from Java 8
Newcomer training using the Web-Basic programming using Java-
Try using the Stream API in Java
[Introduction to Java] How to write a Java program
Hit the Salesforce REST API from Java
Using JavaScript from Java in Rhino 2021 version
A program that calculates factorials from 2 to 100
Connect from Java to MySQL using Eclipse
Try running a Kubernetes Job from Java
Call Java methods from Nim using jnim
Get history from Zabbix server in Java
Try using the Emotion API from Android
Find the difference from a multiple of 10
Try using the Wii remote with Java
If a person from Java learns PHP
I tried to make a program that searches for the target class from the process that is overloaded with Java
[Note] Execute java program in the integrated development environment Eclipse-I tried using git
[Java] Get and display the date 10 days later using the Time API added from Java 8.
[Java] Program example to get the maximum and minimum values from an array
Until you run a Java program with the AWS SDK local to Windows
Learn while making a WEB server Introduction to WEB application development from the basics
Access Forec.com from Java using Axis2 Enterprise WSDL
GetInstance () from a @Singleton class in Groovy from Java
[Java] How to use Thread.sleep to pause the program