SoFunction
Updated on 2025-03-08

Detailed steps for connecting to database in Java

1. JDBC (Java Database Connectivity)

This is the most basic way for Java to connect to databases.

  • Importing database drivers: First, you need to add the JDBC driver for a specific database to the project's dependencies.
  • Loading driver: Use()Method loads the database driver. For example,("");。
  • Establish a connection: Use()Method to establish a connection to the database. For example,String url = "jdbc:mysql://localhost:3306/mydb";，String username = "root";，String password = "password";，Connection connection = (url, username, password);。
  • Execute SQL statement: PassStatementorPreparedStatementObjects perform SQL queries, inserts, updates, or deletes.
  • Process result set: If it is a query operation, the returned one needs to be processedResultSetResult set.
  • Close connection: After the operation is complete, be sure to close the connection, statement, and result set to free up resources.

1. Add dependencies

  • Determine the database type: First, determine the type of database you want to connect to, such as MySQL, Oracle, SQL Server, etc. Different databases require different JDBC drivers.
  • Add dependencies: Add the JDBC driver for the corresponding database to your project. If it is a Maven project, you canAdd dependencies to the file; if it is a Gradle project, it isAdded to the file. For example, for a MySQL database, you can addmysql-connector-javarely.

2. Connect to the database

Java virtual machines can find and load JDBC drivers for specific databases

try {
       ("");
   } catch (ClassNotFoundException e) {
       ();
   }

3. Establish a connection

  • urlis the connection string of the database, wherelocalhostIt is the database server address,3306It is the default port number of MySQL.your_database_nameIt is the name of the database you want to connect to.
  • usernameandpasswordThey are the username and password of the database.
String url = "jdbc:mysql://localhost:3306/your_database_name";
   String username = "your_username";
   String password = "your_password";
   try {
       Connection connection = (url, username, password);
       // After the connection is successful, you can perform subsequent database operations   } catch (SQLException e) {
       ();
   }

4. Execute SQL statements

AvailableStatementorPreparedStatementto execute SQL statements.

StatementExample:

try {
         Statement statement = ();
         ResultSet resultSet = ("SELECT * FROM your_table_name");
         while (()) {
             // Process the result set             int id = ("id");
             String name = ("name");
             ("ID: " + id + ", Name: " + name);
         }
     } catch (SQLException e) {
         ();
     }

If usedPreparedStatement, (can prevent SQL injection):

try {
         String sql = "SELECT * FROM your_table_name WHERE id =?";
         PreparedStatement preparedStatement = (sql);
         (1, 123); // Set parameter values         ResultSet resultSet = ();
         while (()) {
             // Process the result set             int id = ("id");
             String name = ("name");
             ("ID: " + id + ", Name: " + name);
         }
     } catch (SQLException e) {
         ();
     }

5. Process the result set

Depending on the type of SQL statement executed (query, insert, update, delete, etc.), the way the result set is processed is different.

For query statements, useResultSetMethods (such asgetInt、getStringetc.) Get the column values ​​in the result set.

1) Query statement processing

  • useResultSetTraversing the result set:
    • When the query statement is executed, aResultSetObject, which represents the result set returned from the database. AvailablewhileCircular combination()Method to iterate through each row of data in the result set.
    • For example:
try {
         Statement statement = ();
         ResultSet resultSet = ("SELECT * FROM your_table_name");
         while (()) {
             // Process each row of data             int id = ("id");
             String name = ("name");
             ("ID: " + id + ", Name: " + name);
         }
     } catch (SQLException e) {
         ();
     }
  • In a loop, you can use the column name or column index according to the column name and column index.ResultSetThe corresponding method (such asgetInt、getString、getDoubleetc.) to get the value of each column. Column index starts at 1.
  • Get specific types of data:
    • Select the appropriate one based on the data type of the column in the databaseResultSetMethod to get the value.
    • For example, if the column is of integer type, you can usegetInt;If it is a string type, you can use itgetString;If it is a floating point number type, you can use itgetDoublewait.
    • At the same time, a column name or column index can be passed as parameters. For example:
int id = (1); // Get the value of the first column through the column index (integer type)     String name = ("name"); // Get the name by column name"name"The value of the column（String type）

2) Insert statement processing

  • After executing the insert statement, you can usually get the autoincrement primary key value of the insert row (if the database table has an autoincrement primary key):
    • usePreparedStatementExecute the insert statement and set the parameters that return the auto-increment primary key.
    • For example:
String sql = "INSERT INTO your_table_name (column1, column2) VALUES (?,?)";
     try {
         PreparedStatement preparedStatement = (sql, Statement.RETURN_GENERATED_KEYS);
         (1, "value1");
         (2, "value2");
         int rowsInserted = ();
         if (rowsInserted > 0) {
             // Get the auto-increment key             ResultSet generatedKeys = ();
             if (()) {
                 int generatedId = (1);
                 ("Inserted row with id: " + generatedId);
             }
         }
     } catch (SQLException e) {
         ();
     }
  • After performing the insert operation,()Methods can obtain the value containing the autoincrement keyResultSet, and the primary key value of the inserted row can then be obtained from this result set.

3) Update statement processing

  • After executing the update statement, the number of affected rows is usually returned:
    • useStatementorPreparedStatementExecute the update statement and passexecuteUpdateThe return value of the method is used to determine how many rows are updated.
    • For example:
String sql = "UPDATE your_table_name SET column1 =? WHERE column2 =?";
     try {
         PreparedStatement preparedStatement = (sql);
         (1, "newValue1");
         (2, "conditionValue");
         int rowsUpdated = ();
         ("Updated " + rowsUpdated + " rows.");
     } catch (SQLException e) {
         ();
     }
  • If the return value is greater than 0, it means that a row has been updated; if the return value is 0, it means that no row that meets the condition has been updated.

4) Delete statement processing

  • Similar to an update statement, the number of affected rows will also be returned after executing the delete statement:
    • useStatementorPreparedStatementExecute the delete statement and passexecuteUpdateThe return value of the method is used to determine how many rows are deleted.
    • For example:
String sql = "DELETE FROM your_table_name WHERE column1 =?";
     try {
         PreparedStatement preparedStatement = (sql);
         (1, "valueToDelete");
         int rowsDeleted = ();
         ("Deleted " + rowsDeleted + " rows.");
     } catch (SQLException e) {
         ();
     }
  • If the return value is greater than 0, it means that a row has been deleted; if the return value is 0, it means that the row that does not meet the conditions is deleted.

When processing these SQL statements, you need to pay attention to capturingSQLExceptionExceptions to ensure that exceptions can be handled correctly in the event of database errors and perform appropriate error handling and logging.

6. Close the connection

After completing the database operation, be sure to close the connection to free up the resources.

try {
       if (connection!= null) {
           ();
       }
   } catch (SQLException e) {
       ();
   }

2. Database connection pool

Using database connection pools can improve the performance and efficiency of database connections. Common database connection pools include HikariCP, C3P0, Druid, etc.

Taking Druid as an example, first add Druid's dependencies to the project, configure connection pool parameters, such as database URL, username, password, maximum number of connections, etc., and obtain database connections through the connection pool for operation.

1. Add dependencies

If it is a Maven project,Add the following dependencies to:

<dependency>
    <groupId></groupId>
    <artifactId>druid</artifactId>
    <version>1.2.12</version>
</dependency>

If it is a Gradle project,Added in:

implementation ':druid:1.2.12'

2. Basic usage steps

1) Configure the data source:

import ;

   public class DruidExample {
       public static void main(String[] args) {
           // Create Druid data source object           DruidDataSource dataSource = new DruidDataSource();
           ("jdbc:mysql://localhost:3306/your_database");
           ("your_username");
           ("your_password");
           // Other properties can be set, such as maximum number of connections, minimum number of connections, etc.           (5);
           (10);
       }
   }

2) Get the database connection:

try {
       Connection connection = ();
       // Use connection for database operations   } catch (SQLException e) {
       ();
   }

3) Close the data source (usually executed when the application is closed)

();

3. Monitoring function

Druid provides powerful monitoring features, allowing you to access the monitoring pages in the following ways:

  • Configure Druid's monitoring properties in the application:
("stat");
   (true);

Access the monitoring page: By default, it can be accessed throughhttp://localhost:8080/druid/Visit Druid's monitoring page, provided that your application is running on port 8080. In the monitoring page, you can view the status of the connection pool, SQL execution status and other information.

4. Use in combination with Spring

If you use Druid in your Spring project, you can configure it by following the steps:

  • Configure the data source in the configuration file:
&lt;bean  class=""&gt;
       &lt;property name="url" value="jdbc:mysql://localhost:3306/your_database"/&gt;
       &lt;property name="username" value="your_username"/&gt;
       &lt;property name="password" value="your_password"/&gt;
       &lt;!-- Other attribute configuration --&gt;
   &lt;/bean&gt;

Pass in code@AutowiredInject data source:

import ;

   @Service
   public class YourService {
       private DataSource dataSource;

       @Autowired
       public YourService(DataSource dataSource) {
            = dataSource;
       }

       public void doSomethingWithDatabase() {
           try {
               Connection connection = ();
               // Perform database operations           } catch (SQLException e) {
               ();
           }
       }
   }

III. ORM framework

  • Hibernate: Hibernate is a powerful ORM framework that allows mapping database tables into Java objects, simplifying database operations. By configuring Hibernate's configuration files and mapping files for entity classes, you can easily connect to the database and perform various database operations.
  • MyBatis: MyBatis is also a commonly used database access framework. It maps SQL statements to Java methods through SQL mapping files. Although MyBatis is not a strictly ORM framework, it provides a convenient way to manipulate databases, while also allowing custom SQL statements to meet complex business needs.

Here is an example of MyBatis:

1. Add dependencies

If it is a Maven project,Add the following dependencies to:

<dependency>
    <groupId></groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.10</version>
</dependency>

If it is a Gradle project,Added in:

implementation ':mybatis:3.5.10'

At the same time, if you use a database, you also need to add driver dependencies for the corresponding database.

2. Create entity class

For example, there is a user entity class:

public class User {
    private int id;
    private String username;
    private String password;

    // Constructor, getter and setter methods}

3. Create a mapping interface

import ;

public interface UserMapper {
    List<User> getAllUsers();
    User getUserById(int id);
    void insertUser(User user);
    void updateUser(User user);
    void deleteUser(int id);
}

4. Write SQL mapping files

Create an XML file with the same name as the mapping interface, for example：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-////DTD Mapper 3.0//EN"
        "/dtd/">
<mapper namespace="">

    <select  resultType="User">
        SELECT * FROM users;
    </select>

    <select  parameterType="int" resultType="User">
        SELECT * FROM users WHERE id = #{id};
    </select>

    <insert  parameterType="User">
        INSERT INTO users (username, password) VALUES (#{username}, #{password});
    </insert>

    <update  parameterType="User">
        UPDATE users SET username = #{username}, password = #{password} WHERE id = #{id};
    </update>

    <delete  parameterType="int">
        DELETE FROM users WHERE id = #{id};
    </delete>

</mapper>

5. Configure MyBatis

createConfiguration file:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-////DTD Config 3.0//EN"
        "/dtd/">
<configuration>
    <environments default="development">
        <environment >
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value=""/>
                <property name="url" value="jdbc:mysql://localhost:3306/your_database"/>
                <property name="username" value="your_username"/>
                <property name="password" value="your_password"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="your/package/name/"/>
    </mappers>
</configuration>

Load the configuration file in the code and use MyBatis:

import ;
import ;
import ;
import ;

import ;
import ;

public class MyBatisExample {
    public static void main(String[] args) {
        String resource = "";
        InputStream inputStream = null;
        try {
            inputStream = (resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = ();

            // Get the implementation of mapping interface            UserMapper userMapper = ();

            // Query all users            List&lt;User&gt; users = ();
            for (User user : users) {
                (() + ", " + () + ", " + ());
            }

            // Insert the user            User newUser = new User();
            ("newUser");
            ("newPassword");
            (newUser);
            ();

            // Query the user by ID            User userById = (1);
            (() + ", " + () + ", " + ());

            // Update user            User userToUpdate = (2);
            ("updatedUser");
            (userToUpdate);
            ();

            // Delete the user            (3);
            ();

            ();
        } catch (IOException e) {
            ();
        }
    }
}

Summarize

This is the article about the detailed steps of connecting to databases in Java. For more related content on Java database connection, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!