Java -- JDBC 操作mysql数据库

1. Demo1

导包时 不要导具体的mysql包， 为了兼容性，导JDBC 中 sql的包既可以了。

public class Demo1 {/** * @param args * @throws ClassNotFoundException  * @throws SQLException  * @throws Exception  */public static void main(String[] args) throws ClassNotFoundException, SQLException {String url = "jdbc:mysql://localhost:3306/day14";String username = "root";String password = "root";Connection conn = null;Statement st = null;ResultSet rs = null;try{//1.加载驱动(开发推荐的方式)Class.forName("com.mysql.jdbc.Driver");//2.获取与数据库的链接，链接资源有限，尽量晚的创建，尽量早的释放conn = (com.mysql.jdbc.Connection) DriverManager.getConnection(url, username, password);System.out.println(conn);//3.获取用于向数据库发送sql语句的statementst = conn.createStatement();//4.向数据库发sql,并获取代表结果集的resultsetString sql = "select id,name,password,email,birthday from users";rs = st.executeQuery(sql);//5.取出结果集的数据rs.afterLast();rs.previous();System.out.println("id=" + rs.getObject("id"));System.out.println("name=" + rs.getObject("name"));System.out.println("password=" + rs.getObject("password"));System.out.println("email=" + rs.getObject("email"));System.out.println("birthday=" + rs.getObject("birthday"));}finally{//6.关闭链接，释放资源if(rs!=null){try{rs.close();}catch (Exception e) {e.printStackTrace();}rs = null;}if(st!=null){try{st.close();}catch (Exception e) {e.printStackTrace();}}if(conn!=null){try{conn.close();}catch (Exception e) {e.printStackTrace();}}}}}

ResultSet还提供了对结果集进行滚动的方法：

•next()：移动到下一行

•Previous()：移动到前一行

•absolute(int row)：移动到指定行

•beforeFirst()：移动resultSet的最前面。

•afterLast() ：移动到resultSet的最后面。

2. 增、删、改、查 示例

//使用jdbc对数据库增删改查public class Demo4 {@Testpublic void insert(){Connection conn = null;Statement st = null;ResultSet rs = null;try{conn = JdbcUtils.getConnection();st = conn.createStatement();String sql = "insert into users(id,name,password,email,birthday) values(4,'xxx','123','[e-mail]',to_date('1980-09-09','YYYY-MM-DD'))";int num = st.executeUpdate(sql);  //updateif(num>0){System.out.println("插入成功！！");}}catch (Exception e) {e.printStackTrace();}finally{JdbcUtils.release(conn, st, rs);}}@Testpublic void delete(){Connection conn = null;Statement st = null;ResultSet rs = null;try{conn = JdbcUtils.getConnection();String sql = "delete from users where id=4";st = conn.createStatement();int num = st.executeUpdate(sql);if(num>0){System.out.println("删除成功！！");}}catch (Exception e) {}finally{JdbcUtils.release(conn, st, rs);}}@Testpublic void update(){Connection conn = null;Statement st = null;ResultSet rs = null;try{conn = JdbcUtils.getConnection();String sql = "update users set name='wuwang',email='[e-mail]' where id=3";st = conn.createStatement();int num = st.executeUpdate(sql);if(num>0){System.out.println("更新成功！！");}}catch (Exception e) {}finally{JdbcUtils.release(conn, st, rs);}}@Testpublic void find(){Connection conn = null;Statement st = null;ResultSet rs = null;try{conn = JdbcUtils.getConnection();String sql = "select * from users where id=1";st = conn.createStatement();rs = st.executeQuery(sql);if(rs.next()){System.out.println(rs.getString("name"));}}catch (Exception e) {}finally{JdbcUtils.release(conn, st, rs);}}}

工具类

public class JdbcUtils {private static String driver = null;private static String url = null;private static String username = null;private static String password = null;static{try{InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");Properties prop = new Properties();prop.load(in);driver = prop.getProperty("driver");url = prop.getProperty("url");username = prop.getProperty("username");password = prop.getProperty("password");Class.forName(driver);}catch (Exception e) {throw new ExceptionInInitializerError(e);}}public static Connection getConnection() throws SQLException{return DriverManager.getConnection(url, username,password);}public static void release(Connection conn,Statement st,ResultSet rs){if(rs!=null){try{rs.close();}catch (Exception e) {e.printStackTrace();}rs = null;}if(st!=null){try{st.close();}catch (Exception e) {e.printStackTrace();}}if(conn!=null){try{conn.close();}catch (Exception e) {e.printStackTrace();}}}}

配置文件 db.properties ， 配置文件 每行后面不要有空格，propertie解析的时候会将空格也解析。

driver=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/day14username=rootpassword=root#driver=oracle.jdbc.driver.OracleDriver#url=jdbc:oracle:thin:@localhost:1521:orcl#username=root#password=root

2. JavaWeb 工程 结构图