JDBC Tutorials - Herong's Tutorial Examples - v3.12, by Dr. Herong Yang
Installing AdventureWorks Sample Database
This section describes how to install AdventureWorks as a sample database on SQL Server.
To practice JDBC interface with SQL Server, you need some tables and data on the database server. Microsoft has a nice sample database called AdventureWorks available free for you to use. This tutorial shows you how to download and install AdventureWorks on your local SQL Server.
1. Go to the "AdventureWorks installation and configuration" Website at https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure.
2. Click "AdventureWorks2019.bak" in the OLTP section start download and save it to the C:\temp folder.
3. Open command line window as an Adminstrator, and move the downloaded file, AdventureWorks2019.bak, to "C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Backup".
4. Run SQLCMD command line tool to restore AdventureWorks2019 database to the local SQL Server:
herong> sqlcmd -S localhost\SQLEXPRESS -E 1> RESTORE DATABASE AdventureWorks2019 2> FROM disk='C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS \MSSQL\Backup\AdventureWorks2019.bak' 3> WITH 4> MOVE 'AdventureWorks2017' 5> TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS \MSSQL\DATA\AdventureWorks2019.mdf', 6> MOVE 'AdventureWorks2017_log' 7> TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS \MSSQL\DATA\AdventureWorks2019.ldf', 8> REPLACE 9> GO Processed 26344 pages for database 'AdventureWorks2019', file 'AdventureWorks2017' on file 1. Processed 2 pages for database 'AdventureWorks2019', file 'AdventureWorks2017_log' on file 1. RESTORE DATABASE successfully processed 26346 pages in 3.215 seconds (64.019 MB/sec).
When the restore process is completed, two physical database files are installed in the data directory: "C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA".
AdventureWorks2019.mdf 276,824,064 Bytes AdventureWorks2019.ldf 75,497,472 Bytes
The sample database AdventureWorks2019 is ready for you to play now.
By the way, don't follow the instructions given on the Microsoft Website. You will get errors:
4> USE [master] 5> RESTORE DATABASE [AdventureWorks2019] 6> FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER \MSSQL\Backup\AdventureWorks2019.bak' 7> WITH FILE = 1, NOUNLOAD, STATS = 5 8> go Changed database context to 'master'. Msg 5133, Level 16, State 1, Server localhost\SQLEXPRESS, Line 5 Directory lookup for the file "C:\Program Files\Microsoft SQL Server \MSSQL15.SQL2019\MSSQL\DATA\AdventureWorks2019.mdf" failed with the operating system error 3(The system cannot find the path specified). Msg 3156, Level 16, State 3, Server localhost\SQLEXPRESS, Line 5 File 'AdventureWorks2017' cannot be restored to 'C:\Program Files \Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\AdventureWorks2019.mdf'. Use WITH MOVE to identify a valid location for the file. ...
As a reference, here is what I did to install AdventureWorks 2014 database on my Windows computer.
1. Go to the "AdventureWorks installation and configuration" Website at https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure.
2. Download the "Adventure Works 2014 Full Database Backup.zip" file in C:\temp folder.
3. Download the "How to install Adventure Works 2014 Sample Databases.pdf" file. Read the "Install Adventure Works 2014 OLTP database from a backup" section.
4. Unzip "C:\temp\Adventure Works 2014 Full Database Backup.zip" and copy the extracted file AdventureWorks2014.bak to "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup"
5. Run SQLCMD command line tool to restore AdventureWorks2014 database to the local SQL Server:
herong> sqlcmd -S localhost\SQLEXPRESS -U sa -P HerongY@ng 1> RESTORE DATABASE AdventureWorks2014 2> FROM disk='C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS \MSSQL\Backup\AdventureWorks2014.bak' 3> WITH MOVE 'AdventureWorks2014_data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\AdventureWorks2014.mdf', 4> MOVE 'AdventureWorks2014_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\AdventureWorks2014.ldf', 5> REPLACE 6> GO Processed 24248 pages for database 'AdventureWorks2014', file 'AdventureWorks2014_Data' on file 1. Processed 4 pages for database 'AdventureWorks2014', file 'AdventureWorks2014_Log' on file 1. RESTORE DATABASE successfully processed 24252 pages in 4.911 seconds (38.579 MB/sec).
When the restore process is completed, two physical database files are installed in the data directory: "C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA".
AdventureWorks2014.mdf 210,176 KB AdventureWorks2014.ldf 2,048 KB
The sample database AdventureWorks2014 is ready for you to play now.
Table of Contents
JDBC (Java Database Connectivity) Introduction
Installing and Running Java DB - Derby
Derby (Java DB) JDBC DataSource Objects
Java DB (Derby) - DML Statements
Java DB (Derby) - ResultSet Objects of Queries
Java DB (Derby) - PreparedStatement
MySQL JDBC Driver (MySQL Connector/J)
MySQL - Reference Implementation of JdbcRowSet
MySQL - JBDC CallableStatement
MySQL CLOB (Character Large Object) - TEXT
MySQL BLOB (Binary Large Object) - BLOB
Oracle Express Edition Installation on Windows
Oracle - Reference Implementation of JdbcRowSet
Oracle - JBDC CallableStatement
Oracle CLOB (Character Large Object) - TEXT
Oracle BLOB (Binary Large Object) - BLOB
►Microsoft SQL Server Express Edition
What Is Microsoft SQL Server Express Edition
Installing Microsoft SQL Server Express Edition
Installing Microsoft SQL Server 2014 Express Edition
SQLCMD SQL Server Command Line Tool
►Installing AdventureWorks Sample Database
Create Login User in SQL Server
Microsoft JDBC Driver for SQL Server
Microsoft JDBC Driver - Query Statements and Result Sets
Microsoft JDBC Driver - DatabaseMetaData Object
Microsoft JDBC Driver - DDL Statements
Microsoft JDBC Driver - DML Statements
SQL Server - PreparedStatement
SQL Server CLOB (Character Large Object) - TEXT
SQL Server BLOB (Binary Large Object) - BLOB
JDBC-ODBC Bridge Driver - sun.jdbc.odbc.JdbcOdbcDriver
JDBC-ODBC Bridge Driver - Flat Text Files
JDBC-ODBC Bridge Driver - MS Access
JDBC-ODBC Bridge Driver - MS SQL Server
Summary of JDBC Drivers and Database Servers