Java sql database access

Шаг 3. Подтверждение концепции: подключение к SQL с помощью Java

Этот пример следует рассматривать только как подтверждение концепции. Пример кода упрощен для ясности и для него не гарантируется соблюдение рекомендаций корпорации Майкрософт.

Шаг 1. Подключение

Используйте класс подключения для подключения к базе данных SQL.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class SQLDatabaseConnection < // Connect to your database. // Replace server name, username, and password with your credentials public static void main(String[] args) < String connectionUrl = "jdbc:sqlserver://yourserver.database.windows.net:1433;" + "database=AdventureWorks;" + "user=yourusername@yourserver;" + "password=yourpassword;" + "encrypt=true;" + "trustServerCertificate=false;" + "loginTimeout=30;"; try (Connection connection = DriverManager.getConnection(connectionUrl);) < // Code here. >// Handle any errors that may have occurred. catch (SQLException e) < e.printStackTrace(); >> > 

Шаг 2. Выполнение запроса

В этом примере следует подключиться к базе данных SQL Azure, выполнить инструкцию SELECT и вернуть выбранные строки.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class SQLDatabaseConnection < // Connect to your database. // Replace server name, username, and password with your credentials public static void main(String[] args) < String connectionUrl = "jdbc:sqlserver://yourserver.database.windows.net:1433;" + "database=AdventureWorks;" + "user=yourusername@yourserver;" + "password=yourpassword;" + "encrypt=true;" + "trustServerCertificate=false;" + "loginTimeout=30;"; ResultSet resultSet = null; try (Connection connection = DriverManager.getConnection(connectionUrl); Statement statement = connection.createStatement();) < // Create and execute a SELECT SQL statement. String selectSql = "SELECT TOP 10 Title, FirstName, LastName from SalesLT.Customer"; resultSet = statement.executeQuery(selectSql); // Print results from select statement while (resultSet.next()) < System.out.println(resultSet.getString(2) + " " + resultSet.getString(3)); >> catch (SQLException e) < e.printStackTrace(); >> > 

Шаг 3. Вставка строки

В этом примере следует выполнить инструкцию INSERT, передать параметры и извлечь автоматически созданное значение первичного ключа.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; public class SQLDatabaseConnection < // Connect to your database. // Replace server name, username, and password with your credentials public static void main(String[] args) < String connectionUrl = "jdbc:sqlserver://yourserver.database.windows.net:1433;" + "database=AdventureWorks;" + "user=yourusername@yourserver;" + "password=yourpassword;" + "encrypt=true;" + "trustServerCertificate=false;" + "loginTimeout=30;"; String insertSql = "INSERT INTO SalesLT.Product (Name, ProductNumber, Color, StandardCost, ListPrice, SellStartDate) VALUES " + "('NewBike', 'BikeNew', 'Blue', 50, 120, '2016-01-01');"; ResultSet resultSet = null; try (Connection connection = DriverManager.getConnection(connectionUrl); PreparedStatement prepsInsertProduct = connection.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);) < prepsInsertProduct.execute(); // Retrieve the generated key from the insert. resultSet = prepsInsertProduct.getGeneratedKeys(); // Print the ID of the inserted row. while (resultSet.next()) < System.out.println("Generated: " + resultSet.getString(1)); >> // Handle any errors that may have occurred. catch (Exception e) < e.printStackTrace(); >> > 

Источник

2. Java JDBC application :

2.2 JDBC program to connect and query MS Access database/table :

• Once we are ready with above listed things
• Then we can go ahead and code an example to connect MS Access database
• Finally querying database

MsAccessDatabaseConnectionExample.java

package in.bench.resources.msaccess.db.example; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class MsAccessDatabaseConnectionExample < public static void main(String[] args) < // variables Connection connection = null; Statement statement = null; ResultSet resultSet = null; // Step 1: Loading or // registering Oracle JDBC driver class try < Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); >catch(ClassNotFoundException cnfex) < System.out.println("Problem in loading" + " MS Access JDBC driver"); cnfex.printStackTrace(); >// Step 2: Opening database connection try < String msAccessDBName = "D:\\WORKSPACE" + "\\TEST_WORKSPACE\\Java-JDBC\\Player.accdb"; String dbURL = "jdbc:odbc:Driver=" + ";" + "DBQ=" + msAccessDBName + ";DriverID=22;READONLY=true"; // Step 2.A: Create and // get connection using DriverManager class connection = DriverManager.getConnection(dbURL); // Step 2.B: Creating JDBC Statement statement = connection.createStatement(); // Step 2.C: Executing SQL and // retrieve data into ResultSet resultSet = statement .executeQuery("SELECT * FROM PLAYER"); System.out.println("ID\tName\t\t\tAge\tMatches"); System.out.println("==\t================\t===\t======="); // processing returned data and printing into console while(resultSet.next()) < System.out.println(resultSet.getInt(1) + "\t" + resultSet.getString(2) + "\t" + resultSet.getString(3) + "\t" + resultSet.getString(4)); >> catch(SQLException sqlex) < sqlex.printStackTrace(); >finally < // Step 3: Closing database connection try < if(null != connection) < // cleanup resources, once after processing resultSet.close(); statement.close(); // and then finally close connection connection.close(); >> catch (SQLException sqlex) < sqlex.printStackTrace(); >> > >

Output:

ID Name Age Matches == ================ === ======= 1 Sachin Tendulkar 43 200 2 Shane Warne 45 145 3 Kevin Pietersen 36 104 4 Shahid Afridi 36 27 5 Brian Lara 46 131 6 Graeme Smith 36 117 7 Mahela Jayawardene 38 145

2.3 Download :

Related Articles :

• Java – Introduction to JDBC
• Java – JDBC Driver types
• Java – Core JDBC components
• Java – JDBC Driver list for all leading database
• Java – JDBC connection steps
• Java – An example to connect MySQL database
• Java – An example to connect Oracle database
• Java – An example to connect MS Access database
• Java 8 – An example to connect MS Access database in Java 8
• Java – JDBC program to connect IBM DB2 database running on Mainframe z/OS system
• Java – Creating database using JDBC Statement interface
• Java – Droping database using JDBC Statement interface
• Java – Creating a table using JDBC Statement interface
• Java – Inserting a record using JDBC Statement interface
• Java – Getting all list of records using JDBC Statement interface
• Java – Getting single record using JDBC Statement interface
• Java – Updating a record using JDBC Statement interface
• Java – Deleting a record using JDBC Statement interface
• Java – Dropping a table using JDBC Statement interface
• Java – Batch update using JDBC Statement interface
• Java – Batch insert using JDBC Statement interface
• Java – Creating a table using JDBC PreparedStatement interface
• Java – Inserting a record using JDBC PreparedStatement interface
• Java – Getting all list of records using JDBC PreparedStatement interface
• Java – Getting single record using JDBC PreparedStatement interface
• Java – Updating a record using JDBC PreparedStatement interface
• Java – Deleting a record using JDBC PreparedStatement interface
• Java – Batch update using JDBC PreparedStatement interface
• Java – Batch insert using JDBC PreparedStatement interface
• Java – Calling Stored Procedure using JDBC CallableStatement interface
• Java – Calling Stored Function using JDBC CallableStatement interface
• Java – Calling Stored Procedure using JDBC CallableStatement interface with Batch execution
• Java – Transaction handling using JDBC Statement interface
• Java – Transaction handling using JDBC PreparedStatement interface
• Java – Integration with Spring framework (Spring JDBC)
• Java – Where clause example using JDBC Statement interface
• Java – Like clause example using JDBC Statement interface
• Java – Order by clause example using JDBC Statement interface
• Java – Metadata of database using JDBC DatabaseMetaData interface
• Java – Metadata of Resultset using JDBC ResultSetMetaData interface
• Java – Interview question and answer on JDBC

References :

Happy Coding !!
Happy Learning !!

Источник

How to Connect Ms Access Database in Java Using UCanAccess

Welcome Readers, If you are trying really hard to connect your Java application with the MS Access database using the Eclipse and NetBeans IDE and still after numerous attempts you are failing to connect, then you have come to the right place. In this tutorial, we are going to learn about How to Connect MS Access Database in Java or Java 8 using ucanaccess But before moving ahead make sure that you have the latest version of Java in your system and if not then you can download it through this link.

To make it easier for you I have divided this ucanaccess tutorial into several sections and feel free to jump in the division as per your interest.

If you are also interested in Connecting MySQL database to Java, then you can refer to this article How to Connect MySQL Database in Java Using Eclipse.

Without wasting time let’s quickly dive into our tutorial “How to Connect MS Access Database in Java using the Eclipse and NetBeans IDE“.

• 1 How to Connect MS Access Database in Java
  • 1.1 Creating an MS Access Database
  • 1.2 Downloading UCanAccess JAR file
  • 2.1 Step #1 -> Creating a Java Project in Eclipse
  • 2.2 Step #2 -> Adding UCanAccess Jar Files to Java Program in Eclipse
  • 2.3 Step #3 -> Connecting Java Program with the MS Access Database
  • 3.1 Step #1 -> Creating a Java Project in NetBeans
  • 3.2 Step #2 -> Adding UCanAccess Jar Files to Java Program in NetBeans
  • 3.3 Step #3 -> Connecting Java Program with the MS Access Database
  • 3.4 Inserting Data into Table
  • 3.5 Updating Data in the Table
  • 3.6 Retrieving Data from Table
  • 3.7 Deleting Data from the Table
  • 3.8 Conclusion

  How to Connect MS Access Database in Java

  Creating an MS Access Database

  Although you are free to use any of the Microsoft Access version that is convenient for you, I would recommend you to try out the latest one. To get the latest version of the Microsoft Office, you can head for the Official website of Microsft.

  As of now let’s look through the steps involved in creating MS Access database.

  • Open Microsoft office Access (I am using Microsoft office access 2007).
  • Create a New Blank Database as shown in the figure below.

  

  • Give it a name (For example StudentDatabase.accdb) and specify a location where you want to save it.
  • Now click on Create button to create the database.

  

  • As soon as you click Create button, Microsoft Access creates a default table named “Table1” with its default field “ID“.
  • Right-click on the Table and select Design View.

  

  • A Save As dialog box will appear, and it will ask you for to give it a name.

  

  • Provide a suitable name for your table and click OK button.
  • Now you can create fields for your table with their associated data types.(I am going to create two fields Username and City with their data types as Text).

  

  Downloading UCanAccess JAR file

  To connect our Java application with Microsoft Access Database, we need to add some JAR files to our program, and you can download them by clicking on the download link below.

  Connecting MS Access Database in Java Using Eclipse

  Step #1 -> Creating a Java Project in Eclipse

  • Open Eclipse IDE and click on the Java Project under the new section of File Menu ( File>>New>>Java Project ).

  

  • Now give a name to your project (AccessConnect in this example) and click on “Finish”.

  

  • Now right click on the project and create a new Java class (New>>Class).

  

  • Now give a name to your class(AccessConnectivity in this Example), tick mark on the public static void main(String[] args), and then click on the Finish button as shown in the figure below.

  

  Источник

  Java JDBC Example Connect to Microsoft Access Database

  This JDBC tutorial guides you how to develop a Java program that connects to a Microsoft Access Database. In the early days of JDBC, you can connect to an Access database via JDBC ODBC driver provided by JDK. However JDBC ODBC driver is no longer supported so you need to use a third-party JDBC driver for Microsoft Access. And your Java code still uses JDBC API as normal.

  1. Java JDBC Driver for Microsoft Access Database

  There are several third-party JDBC drivers out there for Microsoft Access database, and we recommend UCanAccess — a pure Java JDBC Driver for Access that allows Java developers and JDBC client programs to read/write Microsoft Access databases. UCanAccess supports various Access formats: 2000, 2002/2003, 2007, 2010/2013/2016 (Access 97 is supported for read-only).

  UCanAccess is open-source and implemented entirely in Java so it can be used across platforms (Windows, Mac, Linux…). It also provides Maven dependencies so you can integrate it in your existing projects quickly.

  To use UCanAccess JDBC Driver for Access, add the following dependency information in your project’s pom.xml file:

   net.sf.ucanaccess ucanaccess 4.0.4 

  In case you don’t use Maven, you have to download UCanAccess distribution and add the following JAR files to the classpath:

  • ucanaccess-4.0.4.jar
  • hsqldb-2.3.1.jar
  • jackcess-2.1.11.jar
  • commons-lang-2.6.jar
  • commons-logging-1.1.3.jar

  2. Java JDBC Example with Access Database

  Suppose that we have an Access Database 2007 contains a table Contacts with the following fields:

  

  The database file is located at e:\Java\JavaSE\MsAccess\Contacts.accdb . — This path will be used in database URL. We will write a Java program that uses the UCanAccess JDBC driver to connect to this database, insert a row and select all rows from the table Contacts.

  You can use JDBC API as normal (see Connect to a database with JDBC). The differences lie in the database URL and Access-specific SQL syntax you can use. For example, you need to construct the database URL to include path of the Access database file like this:

  String databaseURL = "jdbc:ucanaccess://e://Java//JavaSE//MsAccess//Contacts.accdb";

  package net.codejava.jdbc; import java.sql.*; /** * This program demonstrates how to use UCanAccess JDBC driver to read/write * a Microsoft Access database. * @author www.codejava.net * */ public class JdbcAccessTest < public static void main(String[] args) < String databaseURL = "jdbc:ucanaccess://e://Java//JavaSE//MsAccess//Contacts.accdb"; try (Connection connection = DriverManager.getConnection(databaseURL)) < String sql = "INSERT INTO Contacts (Full_Name, Email, Phone) VALUES (?, ?, ?)"; PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, "Jim Rohn"); preparedStatement.setString(2, "rohnj@herbalife.com"); preparedStatement.setString(3, "0919989998"); int row = preparedStatement.executeUpdate(); if (row >0) < System.out.println("A row has been inserted successfully."); >sql = "SELECT * FROM Contacts"; Statement statement = connection.createStatement(); ResultSet result = statement.executeQuery(sql); while (result.next()) < int fullname = result.getString("Full_Name"); String email = result.getString("Email"); String phone = result.getString("Phone"); System.out.println(id + ", " + fullname + ", " + email + ", " + phone); >> catch (SQLException ex) < ex.printStackTrace(); >> >

  As you can see, this example looks like trivial JDBC code, the only difference lies in the database URL that needs to include path to the Access database file.

  If you want to see the coding in action, I recommend to watch the video version of this article below:

  References:

  JDBC API References:

  Related JDBC Tutorials:

  About the Author:

  

  Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Java 1.4 and has been falling in love with Java since then. Make friend with him on Facebook and watch his Java videos you YouTube.

  Add comment

  Comments

  No suitable driver found for jdbc:ucanaccess://C://users//gopal//Documents//gopal.accdb
  at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:706)
  at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:252)
  at prj.gopal.OracleResult1.main(OracleResult1.java:14 )
  at java.sql/java.sql.DriverManager.getConnection(Driv erManager.java:251)
  at JdbcAccessTest.main(JdbcAccessTest.java:18)

  java.sql.SQLException: No suitable driver found for jdbc:ucanaccess://ssdc.accdb
  at java.sql/java.sql.DriverManager.getConnection(Driv erManager.java:702)
  at java.sql/java.sql.DriverManager.getConnection(Driv erManager.java:251)
  at JdbcAccessTest.main(JdbcAccessTest.java:18)

  Источник