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

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

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);
            }   
        }
}
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值