数据库为MySQL数据库,Oracle数据库类似:
插入数据
package com.jef.sql; import java.sql.Connection; import java.sql.PreparedStatement; /** * //演示如何使用com.mysql.jdbc连接桥连接Mysql,插入内容,共三种方式:静态的两种executeUpdate(),executeBatch(),动态的PrepareStatement, * 实时动态时可以采用PrepareStatement()和executeBatch()的结合,删除、修改都有这三种方法 */ public class JavaCtMysqlInsert { public static void main(String[] args) { try { Connection ct = ConnectionMySQL.getMySQLConnection(); ct.setAutoCommit(false); PreparedStatement ps = ct.prepareStatement("insert into user values(?, ?)"); ps.setString(1, "ranye"); ps.setString(2, "100"); ps.executeUpdate(); ps.clearParameters(); ct.commit(); ct.close(); } catch (Exception e) { e.printStackTrace(); } } }
package com.jef.sql; import java.sql.Connection; import java.sql.Statement; public class JavaCtMysqlInsertTwo { public static void main(String[] args) { try { Connection ct = ConnectionMySQL.getMySQLConnection(); ct.setAutoCommit(false); Statement sm = ct.createStatement(); sm.addBatch("insert into user values('dage', 100)"); sm.addBatch("insert into user values('haonan', 100)"); sm.executeBatch(); ct.commit(); sm.close(); ct.close(); } catch (Exception e) { e.printStackTrace(); } } }
package com.jef.sql; import java.sql.Connection; import java.sql.Statement; public class JavaCtMysqlInsertThree { public static void main(String[] args) { try { Connection ct = ConnectionMySQL.getMySQLConnection(); ct.setAutoCommit(false); Statement sm = ct.createStatement(); sm.executeUpdate("insert into user values('yuanyuan', '100')"); ct.commit(); sm.close(); ct.close(); } catch (Exception e) { e.printStackTrace(); } } }
删除数据
package com.jef.sql; import java.sql.Connection; import java.sql.Statement; public class JavaCtMysqlDelete { public static void main(String[] args) { try { Connection ct = ConnectionMySQL.getMySQLConnection(); Statement sm = ct.createStatement(); sm.executeUpdate("delete from user where name='tufujie'"); sm.close(); ct.close(); } catch (Exception e) { e.printStackTrace(); } } }
package com.jef.sql; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; public class JavaCtMysqlDeleteTwo { public static void main(String[] args) { try { Connection ct = ConnectionMySQL.getMySQLConnection(); Statement sm = ct.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = sm.executeQuery("select * from user where name='dage'");// 从查询到的内容中进行修改、插入和删除 rs.last(); rs.deleteRow(); rs.close(); sm.close(); ct.close(); } catch (Exception e) { e.printStackTrace(); } } }
修改数据
package com.jef.sql; import java.sql.Connection; import java.sql.Statement; public class JavaCtMysqlUpdate { public static void main(String[] args) { try { Connection ct = ConnectionMySQL.getMySQLConnection(); Statement sm = ct.createStatement(); sm.executeUpdate("update user set name='ran' where name='ranye'"); sm.close(); ct.close(); } catch (Exception e) { e.printStackTrace(); } } }
查询数据
package com.jef.sql; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; /** * 当前情况下至少需要4+2条数据存在,特殊场景需捕获或者抛出 */ public class JavaCtMysqlSelect { public static void main(String[] args) { try { Connection conn = ConnectionMySQL.getMySQLConnection(); Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet result = stmt.executeQuery("select * from user"); System.out.println("从末尾数据开始输出"); result.last(); System.out.println(result.getString("name") + "\t" + result.getString("password")); while (result.previous()) System.out.println(result.getString("name") + "\t" + result.getString("password")); System.out.println("从起始数据开始输出"); result.first(); System.out.println(result.getString("name") + "\t" + result.getString("password")); while(result.next()) System.out.println(result.getString("name") + "\t" + result.getString("password")); // 指定第几笔数据 System.out.println("指定第几笔数据,这里指定第4笔数据"); result.absolute(4); System.out.println(result.getString("name") + "\t" + result.getString("password")); // 从目前游标处指定游标下移数 System.out.println("从目前游标处指定游标位置,这里向下移动2笔数据"); result.relative(2); System.out.println(result.getString("name") + "\t" + result.getString("password")); result.close(); stmt.close(); conn.close(); } catch (Exception e) { e.printStackTrace(); } } }