mysql05

shell操作mysql

#!/bin/sh
MYSQL="mysql -hmaster -uroot -p123456"
sql="select * from shujia.student where sex='0'"
result="$($MYSQL -e "$sql")"
echo  "$result"

java操作mysql

导入第三方工具包
	file->project structure->Modules->Dependencies->+->JARs or dir...->选择包->apply

1.加载第三方工具
	Class.forname("com.mysql.jdbc.Driver");
2.获取连接
    String url="jdbc:mysql://master:3306/shujia";
    String username="root";
    String password="123456";
    Connection connection = DriverManager.getConnection(url, username, password);
3.获取执行器 createStatement(会出现sql注入不使用)和prepareStatement
	PreparedStatement ps = connection.prepareStatement(sql);//给sql的格式(模板)
    ps.setString(1,"1012");
    ps.setString(2,"test");
4.获取结果(sql语句为增删改查操作,不需要解析结果,使用executeUpdate())
	ResultSet rs = ps.executeQuery();
    while (rs.next()){
        String name = rs.getString("name");
        System.out.println(name);
    }
5.关闭连接(从下向上关闭)
	rs.close();
	ps.close();
	conn.close();

sql注入:参数中有mysql命令,而mysql把这些关键字当做命令去执行

    prepareStatement:避免了sql注入,首先发送sql的格式,然后在传递参数(参数中有关键字也作为参数执行)
    prepareStatement传参:通过set数据类型(int prepareIndex,数据类型 x)
注意:index从1开始
    Class.forName("com.mysql.jdbc.Driver"); 
    Connection connection=DriverManager.getConnection("jdbc:mysql://master:3306/shujia"
            , "root", "123456");
    String sql="select * from user where username=?";
    PreparedStatement statement = connection.prepareStatement(sql);
    statement.setString(1, username);
    ResultSet rs = statement.executeQuery();

创建连接和关闭需要写很多次,可以把连接和关闭写入到工具类中,再次使用时,直接调用工具类,避免多次书写创建连接和关闭

public class JDBCUtil {
  private static String driver="com.mysql.jdbc.Driver";
  private static String url="jdbc:mysql://master:3306/shujia";
private static String username="root";
private static String password="123456";
static {
    try {
        Class.forName(driver);
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
}
//获取连接的方法
public static Connection getConnection() throws Exception {
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
}
public static void closeAll(PreparedStatement ps,Connection conn)throws Exception{
    ps.close();
    conn.close();
}
public static void closeAll(ResultSet rs,PreparedStatement ps, Connection conn)throws Exception{
    rs.close();
    ps.close();
    conn.close();
}

}

创建资源目录

1.创建普通目录(建议名称为 resource)
2.通过project structure设置为资源目录

创建配置文件(创建在资源目录中)

 格式必须后缀为properties