1.使用jdbc连接数据库：
a.导入驱动jar包 b.Class.forName(DRIVER); // 2.建立连接 con = DriverManager.getConnection(URL, USERNAME, PWD);
2.增、删、改：（只要修改sql就可实现） import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement;
public class UpadateTest { public static final String DRIVER = "com.mysql.jdbc.Driver"; public static final String URL = "jdbc:mysql://localhost:3306/petstore"; public static final String USERNAME = "root"; public static final String PWD = "root";
public static void main(String[] args) { Connection con = null; Statement statement = null; try { Class.forName(DRIVER); con = DriverManager.getConnection(URL, USERNAME, PWD); statement = con.createStatement(); String sql = "update pet set name='毛毛' where id=2"; int result = statement.executeUpdate(sql);//增删改 if (result > 0) { System.out.println("修改成功"); } else { System.out.println("修改失败"); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }finally{ try { statement.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
3.查询： import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement;
public class QueryTest { public static final String DRIVER = "com.mysql.jdbc.Driver"; public static final String URL = "jdbc:mysql://localhost:3306/petstore"; public static final String USERNAME = "root"; public static final String PWD = "root";
public static void main(String[] args) { Connection con = null; Statement statement = null; ResultSet rs=null; try { Class.forName(DRIVER); con = DriverManager.getConnection(URL, USERNAME, PWD); statement = con.createStatement(); String sql = "select * from pet"; rs = statement.executeQuery(sql); while (rs.next()) { System.out.print(rs.getInt(1)+"\t"); System.out.print(rs.getString(2)+"\t"); System.out.print(rs.getInt(3)+"\t"); System.out.print(rs.getInt(4)+"\t"); System.out.print(rs.getString(5)+"\n"); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }finally{ try { rs.close(); statement.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } }
} }
4.statement的方法的区别： execute 不常用 返回值是boolean类型，true指返回结果集 executeUpdate 增、删、改 返回int类型，指受影响的行数 executeQuery 查 返回ResultSet类型，指结果集
5.prepareStatement避免sql注入异常 使用方式： import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List;
public class PrepTest { public static final String DRIVER = "com.mysql.jdbc.Driver"; public static final String URL = "jdbc:mysql://localhost:3306/petstore"; public static final String USERNAME = "root"; public static final String PWD = "root";
public static void main(String[] args) { Connection con = null; PreparedStatement ps = null; ResultSet rs = null; List<Master> masterlst = new ArrayList<Master>(); try { // 1加载驱动 Class.forName(DRIVER); // 2创建连接 con = DriverManager.getConnection(URL, USERNAME, PWD); // 3声明Sql // StringBuffer sql = new StringBuffer("select * from master where 1=1 "); // sql.append("and name=? "); // sql.append("and money=?"); String sql="select * from master "; // 4.创建PreparedStatement对象 ps = con.prepareStatement(sql); // 5.传参数 // ps.setString(1, "李"); // ps.setInt(2, 100); // 6.执行sql rs = ps.executeQuery(); // 7遍历显示 while (rs.next()) { Master m = new Master(); m.setId(rs.getInt(1)); m.setName(rs.getString(2)); m.setPassword(rs.getString(3)); m.setMoney(rs.getInt(4)); masterlst.add(m); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { // 8.关闭对象//由内到外关闭 try { rs.close(); ps.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } }
System.out.println("查询结果："); for (Master m:masterlst) { System.out.println(m.getId()+"\t"+m.getName()+"\t"+m.getPassword()+"\t"+m.getMoney()); } }
}
转载于:https://www.cnblogs.com/lovel/p/7215490.html
相关资源：JDBC数据库编程实验