快速入手Jdbc并实现最简单的demo

快速入手Jdbc并实现最简单的demo

1.jdbc操作oracle数据库

• 需要的依赖

<!-- 这个想办法自己找资源 需要可以联系我wx:18262717718-->
<dependency>
      <groupId>com.oracle</groupId>
       <artifactId>ojdbc14</artifactId>
       <version>10.2.0.1</version>
</dependency>

• demo示例

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class JdbcDemo {
public void getDataList() {
    Connection connection=null;
    Statement stmt=null;
    ResultSet rs=null;
    try {
        //（1）Class.forName()加载驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //（2）DriverManager.getConnection(URL,用户名,密码)获得数据库连接 (Connection)
        connection=DriverManager.getConnection("jdbc:oracle:thin:(这里写自己oracle服务器的ip地址):orcl","airquality","airquality");
        //（3）获得Statement对象，执行SQL语句
        String sql="select name from POIS_NEW  where rownum < 10";
        stmt=connection.createStatement();
        rs=stmt.executeQuery(sql);
        //(5)处理结果集
        while(rs.next()) {
            String NAME = rs.getString("name");
            System.out.println(NAME);
        }
        //(4).释放资源
        rs.close();
        stmt.close();
        connection.close();
        System.out.println("JdbcDemo1.main()");
        
    } catch (Exception e) {
        // TODO: handle exception
    }
}
public static void main(String[] args) {
    JdbcDemo jdbcDemo=new JdbcDemo();
    jdbcDemo.getDataList();
}
}

2.jdbc操作mysql数据库

• 需要的依赖

<dependency> 
   <groupId>mysql</groupId>  
   <artifactId>mysql-connector-java</artifactId>
   <version>5.1.30</version> 
 </dependency>

• demo示例

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


/** Jdbc的快速入门程序
*/
public class JdbcDemo1 {
    public static void main(String[] args) throws Exception {
        //1.注册数据库驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取数据库连接
        Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/jt_db?characterEncoding=utf-8",
                "root", "root");
        //3.获取传输器
        Statement stat = conn.createStatement();
        //4.发送sql到服务器执行,并返回结果
        String sql = "select * from account";
        ResultSet rs = stat.executeQuery(sql);
        //5.处理结果(打印到控制台)
        while ( rs.next() ) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            double money = rs.getDouble("money");
            System.out.println(
                    id+" : "+name+" : "+money );
        }
        //6.释放资源
        rs.close();
        stat.close();
        conn.close();
        System.out.println("JdbcDemo1.main()");
    }
}

3.jdbc的crud(数据库用的是mysql)

直接上代码

• jdbc连接等操作封装成工具类

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Jdbc工具类
 */
public class JdbcUtil {
	/***
	 * 注册驱动并获取连接
	 * @return Connection 连接对象
	 * @throws Exception
	 */
	public static Connection getConn() throws Exception {
		//1.注册数据库驱动
		Class.forName("com.mysql.jdbc.Driver");
		//2.获取数据库连接
		Connection conn = DriverManager.getConnection(
				"jdbc:mysql:///jt_db?characterEncoding=utf-8", 
				"root", "root");
		return conn;
	}
	
	/**
	 * 释放Jdbc程序中的资源
	 * @param conn 连接对象
	 * @param stat 传输器对象
	 * @param rs 结果集对象
	 */
	public static void close(
		Connection conn, Statement stat, ResultSet rs) {
		//6.释放资源
		if( rs != null ) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				rs = null;
			}
		}
		if( stat != null ) {
			try {
				stat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				stat = null;
			}
		}
		if( conn != null ) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				conn = null;
			}
		}
	}
	
	
}

• demo示例

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import org.junit.Test;

import com.tedu.util.JdbcUtil;

/**
 * Jdbc的增删改查
 * 	CRUD：C(create) R(Retrieve) U(update) D(delete)
 */
public class JdbcCRUD {
	
	/** 1、新增：往account表中插入一条新的记录，
	 * name为 "john"，money为3000 */
	@Test
	public void add( ) {
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		try {
			//注册驱动并获取连接
			conn = JdbcUtil.getConn();
			//获取传输器并执行sql语句
			stat = conn.createStatement();
			String sql = "insert into account values "
					+ "(null, 'john', 3000)";
			int rows = stat.executeUpdate( sql );
			System.out.println("影响行数: "+rows);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			//调用工具方法释放资源
			JdbcUtil.close(conn, stat, rs);
		}
		
	}
	/** 2、修改：修改account表中name为 "john" 
	 * 的记录，将金额改为1500 */
	@Test
	public void update( ) {
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		try {
			//注册驱动并获取连接
			conn = JdbcUtil.getConn();
			//获取传输器并执行sql语句
			stat = conn.createStatement();
			String sql = "update account set money=1500"
					+ " where name='john' ";
			int rows = stat.executeUpdate( sql );
			System.out.println( "影响行数: "+rows );
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(conn, stat, rs);
		}
	}
	/** 3、删除：删除account表中name为 "john" 的记录 */
	@Test
	public void delete( ) {
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		try {
			//注册驱动并获取连接
			conn = JdbcUtil.getConn();
			//获取传输器并执行sql语句
			stat = conn.createStatement();
			String sql = "delete from account where "
					+ "name='john'";
			int rows = stat.executeUpdate( sql );
			System.out.println( "影响行数: "+rows );
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(conn, stat, rs);
		}
	}
	/** 4、查询：查询account表中id为1的记录 */
	@Test
	public void findById( ) {
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		try {
			//注册驱动并获取连接
			conn = JdbcUtil.getConn();
			//获取传输器并执行sql
			stat = conn.createStatement();
			String sql = "select * from account where id=1";
			rs = stat.executeQuery( sql );
			//处理结果
			if(rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				double money = rs.getDouble("money");
				System.out.println(id+" : "+name+" : "+money);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

4.后续说明

• 连接池的使用
• 传输器对象PreparedStatement的使用(防止sql注入攻击)
• 最后别忘了释放资源