JDBC连接oracle数据库进行增，删，改，查

1.配置相关maven环境

附上链接

2.建立实体类

这里我建立一个实体类映射数据库中的XWD_TEST表

public class ModelOfJDBC {
	private int id;
	
	private String name;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}	
}

3.配置数据源并连接到数据库

url为你要连接的数据库地址，username和password为登录名和密码。

在这里我建立了一个static connection对象供后面的增删改查使用。同时将资源关闭的方法进行了封装。

public class Data_By_JDBC {

	public static final String url = "jdbc:oracle:thin:@172.30.0.242:1520:ORADB01"; 
	
	public static final String username = "dev_fspf_omms_n";
	
	public static final String password = "dev_fspf_omms_n";
	
	public static  Connection connection = null;//
	
	/**
	 * 加载oracle驱动
	 */
	static {
		try
		{
			Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();//加载oracle驱动
			System.out.println("oracle驱动加载中...");
			connection = getConnection();
			System.out.println("连接数据库成功");
		}
		catch(InstantiationException e1)
		{
			System.out.println("实例化异常");
		}
		catch(IllegalAccessException e2)
		{
			System.out.println("权限异常");
		}
		catch(ClassNotFoundException e3)
		{
			System.out.println("找不到类");
		}
	}
	
	/***
	 * 获取connection连接
	 * @return
	 */
	public static Connection getConnection()
	{
	   
	    try
	    {
	        connection = DriverManager.getConnection(url, username, password);//获取连接，使用DriverManager的getConnection方法
	        return connection;
	    }
	    catch (Exception e)
	    {
	        e.printStackTrace();
	        return null;
	    }
	}

	/**
	 * 关闭资源
	 */
	public static void close(ResultSet rs, Connection connection, PreparedStatement ps) {
		try {
			closeResultSet(rs);
			closePreparedStatement(ps);
			closeConnection(connection);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	//关闭结果集
	public static void closeResultSet(ResultSet rs) throws SQLException {
		if(rs!=null) {
			rs.close();
		}
	}
	//关闭连接
	public static void closeConnection(Connection connection) throws SQLException {
		if(connection!=null) {
			connection.close();
		}
	}
	//关闭预编译
	public static void closePreparedStatement(PreparedStatement ps) throws SQLException {
		if(ps!=null) {
			ps.close();
		}
	}
}

4.对数据库进行增删改查

在进行增删改查之前，一般会对sql语句（字符串存储）预编译到PreparedStatement对象中（PreparedStatement ps = connection.prepareStatement(sql语句)）。在操作完成后，不要忘记对这两个对象释放资源。

然后使用 ps.executeQuery();执行sql语句，如果有返回结果，将返回结果存放在ResultSet对象中。

• 查询操作（select *）​​​​​​​

        建立PreparedStatement对象和ResultSet对象

public class Data_Select extends Data_By_JDBC {

	/**
	 * select操作
	 * @param args
	 */
	public static void selectAll() {
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select * from XWD_TEST ";
		
		try {
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			List<ModelOfJDBC> list = new ArrayList<>();
			System.out.println("ID"+" "+"NAME");
			while (rs.next()) {
				ModelOfJDBC moj = new ModelOfJDBC();
				System.out.print(rs.getInt("ID")+" ");
				moj.setId(rs.getInt("ID"));
				System.out.println(rs.getString("NAME"));
				moj.setName(rs.getString("NAME"));
				list.add(moj);
			}
			closeResultSet(rs);
			System.out.println("关闭select的结果集");
			closePreparedStatement(ps);
			System.out.println("关闭select的预编译");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
	}
}

• ​​​​​​​更新操作

public class Data_Update extends Data_By_JDBC {

	/**
	 * update操作
	 */
	public static void update() {
		String sql = "update XWD_TEST set name = 'liyi' where name = 'li'";
		try {
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.executeQuery();//执行sql语句
			System.out.println("update 成功");
			closePreparedStatement(ps);
			System.out.println("关闭update预编译");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

• 删除操作

public class Data_delete extends Data_By_JDBC {
	
	/**
	 * 删除操作
	 */
	public static void delete() {
		String sql = "delete from XWD_TEST where id = 3";
		try {
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.executeQuery();//执行sql语句
			System.out.println("ִ删除成功");
			closePreparedStatement(ps);
			System.out.println("关闭delete预编译");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

• 插入操作

public class Data_Insert extends Data_By_JDBC {

	/**
	 * insert操作
	 * @param args
	 */
	public static void insert() {
		String sql = "insert into XWD_TEST values(3,'xue')";
		try {
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.executeQuery();//执行插入语句
			System.out.println("插入成功");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
	}
	
}

5.运行

在代码最后释放公用的connection资源。

public class Run extends Data_By_JDBC {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		
//		Data_delete.delete();
//		Data_Update.update();
//		Data_Select.selectAll();
		
		try {
			closeConnection(connection);//关闭connection
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}