1.java中提供接口为多种数据库提供规范
- 具体实现接口类由各数据厂商提供
2.开发步骤
1.注册驱动.2.获得连接.
3.获得语句执行平台4.执行sql语句
5.处理结果6.释放资源.
---------------------
不使用java.sql.DriverManager.registerDriver(new Driver());
是因为registerDriver同样有new会注册两次
Class.forName("com.mysql.jdbc.Driver");
String urlString=
"jdbc:mysql://localhost:3306/myabc",
usename = "root",
password = "0616";
Connection co = DriverManager.getConnection(urlString, usename,password);
String sql = "SELECT * FROM datausers ";
Statement st = co.createStatement();
ResultSet rest = st.executeQuery(sql);
List<Datausers> arr=new ArrayList<Datausers>();
while (rest.next()) {
Datausers use=new Datausers(rest.getString("dser"), rest.getString("dpassword"));
arr.add(use);
}
co.close();
st.close();
rest.close();
使用Statement 会产生注入攻击密码后加入'or'1=1就会为真
-----------------------使用PreparedStatement 不会产生注入攻击并且速度快
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
String urlString = "jdbc:mysql://localhost:3306/myabc", usename = "root", password = "0616";
Connection co = DriverManager.getConnection(urlString, usename,
password);
String sql = "SELECT * FROM datausers WHERE dser=?AND dpassword=?or 1=1";
PreparedStatement ps = co.prepareStatement(sql);
Scanner sc = new Scanner(System.in);
String user = sc.nextLine();
String pass = sc.nextLine();
ps.setObject(1, user);
ps.setObject(2, pass);
ResultSet rest = ps.executeQuery();上处使用sql已经初始化此处就不用了
while (rest.next()) {
System.out.println(rest.getObject("dser") + " "
+ rest.getObject("dpassword"));
}
rest.close();
ps.close();
co.close();
}
executeQuery()返回ResultSet可以处理结果
而st.executeUpdate()会返回int仅可以做insert，delete，update
3.注册，close重复代码多，写成tool类
public class Propertool {
private static Connection co;
static {
try {
Class.forName("com.mysql.jdbc.Driver");
String urlString = "jdbc:mysql://localhost:3306/myabc", usename = "root", password = "0616";
co = DriverManager.getConnection(urlString, usename, password);
} catch (Exception ex) {
throw new RuntimeException(ex + "连接数据库失败");
}
}
private Propertool() {
}
public static Connection getConnection() {
return co;
}
public static void close(Connection co, Statement st, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (Exception e) { }
}
if (co != null) {
try {
co.close();
} catch (Exception e) {
}
}
if (st != null) {
try {
st.close();
} catch (Exception e) {
// TODO: handle exception
}
}
}
4.将url等写成曾配置文件
- newfile文件，注意空格问题（可能在句末）
- 文件放在src下，如此会在创建时同样在bin目录下创建（bin是放编译后class文件地方）
drivername=com.mysql.jdbc.Driver
urlString=jdbc:mysql://localhost:3306/myabc
usename=root
password=0616
---------------------------用Properties读取
public class sqlBin {
public static void main(String[] args) throws Exception{
InputStream in= sqlBin.class.getClassLoader().getResourceAsStream("databases.properties");
Properties pro=new Properties();
pro.load(in);
String drivername= pro.getProperty("drivername");
String urlString= pro.getProperty("urlString");
String usename=pro.getProperty("usename");
String password=pro.getProperty("password");
System.out.println(drivername+ " "+password+" "+urlString +" "+usename);
Class.forName(drivername);
Connection con=DriverManager.getConnection(urlString, usename, password);
System.out.println(con);
con.close();
}}
在使用java连接时候需要打开mysql服务（net start mysql）
-----------------------
实际使用时候需要将本类写成工具类，在代码块中static{}中调取方法
public class sqlBin {
private static String drivername;
private static String urlString;
private static String usename;
private static String password;
private static Connection con;
static
{
try {
getConfig();
Class.forName(drivername);
con=DriverManager.getConnection(urlString, usename, password);
} catch (Exception e) {
throw new RuntimeException("连接数据库失败"); }
}
private static void getConfig ()throws Exception
{
InputStream in= sqlBin.class.getClassLoader().getResourceAsStream("databases.properties");
Properties pro=new Properties();
pro.load(in);
drivername= pro.getProperty("drivername");
urlString= pro.getProperty("urlString");
usename=pro.getProperty("usename");
password=pro.getProperty("password");
}
