java jdbc连接各种数据库配置步骤，如：Oracle、MySql、SQLserver等数据库

最新推荐文章于 2023-01-04 00:26:47 发布

持之以~恒

最新推荐文章于 2023-01-04 00:26:47 发布

阅读量1k

点赞数

分类专栏： ORACLE 学习数据库文章标签： java jdbc properties文件配置连接数据库

本文链接：https://blog.csdn.net/qq_34693104/article/details/81430728

版权

ORACLE 学习同时被 2 个专栏收录

11 篇文章 0 订阅

订阅专栏

数据库

2 篇文章 0 订阅

订阅专栏

配置之前大家先了解一下各种数据库连接配置，可以上网查相关文件，以下是一些常用数据库的连接配置。

jdbc.properties各种数据库连接配置：

Oracle

jdbc.driverClassName=oracle.jdbc.OracleDriver
jdbc.url=jdbc:oracle:thin:@localhost:1521:orcl
jdbc.username=scott
jdbc.password=tiger

MySQL

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test
jdbc.username=root
jdbc.password=root

MS SQL Server 2000 (Microsoft)

jdbc.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc.url=jdbc:sqlserver://192.168.1.130:1433;databaseName=test
jdbc.username=sa
jdbc.password=sa

HSQLDB

jdbc.driverClassName=org.hsqldb.jdbcDriver
jdbc.url=jdbc:hsqldb:hsql://localhost:9001/bookstore
jdbc.username=sa
jdbc.password=

PostgreSQL

jdbc.driverClassName=org.postgresql.Driver
jdbc.url=jdbc:postgresql://localhost/bookstore
jdbc.username=
jdbc.password=

MS SQL Server 2000 (JTDS)

jdbc.driverClassName=net.sourceforge.jtds.jdbc.Driver
jdbc.url=jdbc:jtds:sqlserver://localhost:1433/bookstore
jdbc.username=
jdbc.password=

ODBC

jdbc.driverClassName=sun.jdbc.odbc.JdbcOdbcDriver
jdbc.url=jdbc:odbc:bookstore
jdbc.username=
jdbc.password=

下面以连接Oracle数据库为例：

第一步：

先将oracle连接数据库jar包引用到项目中，驱动包可以在本机Oracle安装路径里面找，本人的是：E:\app\Administrator\product\11.2.0\dbhome_1\jdbc\lib，该文件夹下有你需要的jar包，我选择的是ojdbc6.jar。

在项目的src目录下新建一个配置文件db.properties用来存放连接的内容

oracledb.properties

driverClass=oracle.jdbc.driver.OracleDriver jdbcUrl=jdbc:oracle:thin:@localhost:1521:orcldb userName=scott password=tiger

User.java :

package com.lht.jdbc;

public class User {
private Integer userId;
private String userName;
private String password;

public User(){

}
public User(int userId, String userName, String password) {
    this.userId = userId;
    this.userName = userName;
    this.password = password;
}
public Integer getUserId() {
    return userId;
}

public void setUserId(Integer userId) {
    this.userId = userId;
}

public String getUserName() {
    return userName;
}

public void setUserName(String userName) {
    this.userName = userName;
}

public String getPassword() {
    return password;
}

public void setPassword(String password) {
    this.password = password;
}
@Override
public String toString() {
    return "User [userId=" + userId + ", userName=" + userName
            + ", password=" + password + "]";
    }
}

sql脚本：

CREATE TABLE "SCOTT"."TB_USER" ( "USER_ID" NUMBER(8,0) PRIMARY KEY, "USER_NAME" VARCHAR2(50), "USER_PWD" VARCHAR2(25) );
INSERT INTO TB_USET VALUES(1,’张三’,’123456’);

第二步：

写DBUtil工具类用来获取数据库连接：

package com.lht.jdbc;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
/**
 * 提供获取连接和释放资源方法
 * @author Administrator
 *
 */
public class DBUtil {

private static String driverClass = null;
private static String jdbcUrl = null;
private static String userName = null;
private static String password = null;

/**
 * 静态代码块加载配置文件信息
 */
static {
    try {
        //1.通过当前类获取类加载器
        ClassLoader classLoader =  DBUtil.class.getClassLoader();

        //2.通过类加载器的方法获得一个输入流,要切换数据库只需修改properties文件名即可
        InputStream is = classLoader.getResourceAsStream("oracledb.properties");
        //InputStream is = classLoader.getResourceAsStream("mysqldb.properties");

        //3.创建一个properties对象
        Properties properties = new Properties();

        //4.加载输入流
        properties.load(is);

        //5.获取相关参数值
        driverClass = properties.getProperty("driverClass");
        jdbcUrl = properties.getProperty("jdbcUrl");
        userName = properties.getProperty("userName");
        password = properties.getProperty("password");
    } catch (IOException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
    }
}

/**
 * 获取连接方法
 * 
 * @return
 */
public static Connection getConnection() {
    Connection conn = null;     
    try {
        //加载驱动
        Class.forName(driverClass);

        //得到连接
        conn = DriverManager.getConnection(jdbcUrl, userName, password);

    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return conn;
}

/**
 * 释放资源
 * 
 * @param pstmt
 * @param rs
 * @param conn
 */
public static void close(PreparedStatement pstmt,ResultSet rs,Connection conn){
    if(pstmt !=null){
        try {
            pstmt.close();
        } catch (SQLException e) {

            e.printStackTrace();
        }
    }
    if(rs !=null){
        try {
            rs.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    if(conn != null){
        try {
            conn.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        }
    }

}

第三步：

编写测试类：CRUDTest 类；实现对数据库表的增、删、改、查操作

CRUDTest.java ：

package com.lht.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

public class CRUDTest {
private Connection conn=null;
private PreparedStatement pstmt=null;
private ResultSet rs=null;//定义存放查询结果的结

    /**
     * 添加操作方法
     */
    @Test
    public void addUserTest() {
        User user = new User(2,"张三","123");
        try {
            //1. 获取连接
            conn = DBUtil.getConnection();
            //2. 编写SQL语句
            String sql = "insert into tb_user(user_id,user_name,user_pwd) values(?,?,?)";
            //3. 获取执行SQL执行对象
            pstmt = conn.prepareStatement(sql);
            //4. 设置参数
            pstmt.setInt(1,user.getUserId());
            pstmt.setString(2, user.getUserName());
            pstmt.setString(3, user.getPassword());

            //5. 执行插入操作
            int row = pstmt.executeUpdate();

            if(row > 0) {
                System.out.println("添加用户成功！");
            } else {
                System.out.println("添加用户失败！");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            //6. 释放资源
            DBUtil.close(pstmt, rs, conn);
        }

    }
    /**
     * 查询操作方法
     *根据id查询用户信息
     */
    @Test
    public void findUserByIdTest(){
        String sql = "select * from tb_user where user_id=?";
        User user= null;
        try{
            conn=DBUtil.getConnection();
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,2);
            rs=pstmt.executeQuery();//执行查询操作
            if(rs.next()){              
                user=new User();
                user.setUserId(rs.getInt("user_id"));
                user.setUserName(rs.getString("user_name"));
                user.setPassword(rs.getString("user_pwd"));     
            }
            System.out.println(user.toString());
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            //按顺序进行关闭
            DBUtil.close(pstmt, rs, conn);

        }
    }

    /**
     * 更新数据操作方法
     */
    @Test
    public void updateUserByTest() {

        User user = new User(2,"李四","123456");
        try {
            conn = DBUtil.getConnection();
            String sql = "update tb_user set "
                    + "user_name=?,user_pwd=? where user_id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, user.getUserName());
            pstmt.setString(2, user.getPassword());
            pstmt.setInt(3, user.getUserId());

            int row = pstmt.executeUpdate();

            if(row > 0) {
                System.out.println("更新用户成功！");
            } else {
                System.out.println("更新用户失败！");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            //6. 释放资源
            DBUtil.close(pstmt, rs, conn);
        }   
    }
    /**
     * 删除数据操作方法
     */
    @Test
    public void deleteUserByIdTest() {

        try {
            conn = DBUtil.getConnection();
            String sql = "delete from tb_user where user_id=?";

            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,2);
            int row = pstmt.executeUpdate();
            if(row > 0) {
                System.out.println("删除用户成功！");
            } else {
                System.out.println("删除用户失败！");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            //6. 释放资源
            DBUtil.close(pstmt, rs, conn);
            }   
        }
}