本文主要是介绍一起重新开始学大数据-MySQL篇-Day37-sql(5)-shell操作MySQL,java操作MySQL、附带JDBC的NullPointerException,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
一起重新开始学大数据-MySQL篇-Day37-sql(5) |
shell操作mysql |
shell操作mysql
#!/bin/sh
MYSQL="mysql -hmaster -uroot -p123456"
sql="select * from bigdata.students where sex='0'"
result="$($MYSQL -e "$sql")"
echo -e "$result"
java操作mysql |
导入第三方工具包
file->project structure->Modules->Dependencies->+ ->JARs or dir…->选择包->apply
创建一个demo1试试看
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
// Statement statement = conection.createStatement();
// ResultSet rs = statement.executeQuery(sql);String sql="select * from usr where id=? and name=?";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开始
创建连接和关闭需要写很多次,可以把连接和关闭写入到工具类中,再次使用时,直接调用工具类,避免多次书写创建连接和关闭
public class JDBCUtil {private static String driver="com.mysql.jdbc.Driver";private static String url;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();}
}
创建资源目录
背景Ⅰ:由于如果每查一次数据库,都要对数据库连接登录等操作,这样就会浪费精力,如果将这些操作进行封装起来,就能更好地节约时间
背景Ⅱ:如果使用同一种对数据库的访问代码,但由于修改了数据库服务器的ip或者访问的用户密码,代码就不能使用了
1.创建普通目录(建议名称为 resource)
2.通过project st…设置为资源目录
创建配置文件(创建在资源目录中)
1.格式必须后缀为properties
将登入时所需填写的数据写入到这个文件中(如果更换ip,或者密码,只需在这个文件里修改,代码依旧可用)
driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://master:3306/bigdata"
user="root"
password="123456"
完整的jdbc工具类代码
package MySQL;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;public class JDBCUtil {private static String Driver;private static String url;private static String username;private static String password;static {//静态代码块try{ClassLoader classLoader = JDBCUtil.class.getClassLoader();InputStream is = classLoader.getResourceAsStream("test.properties");Properties properties = new Properties();try {properties.load(is);}catch (Exception e){e.printStackTrace();}Driver=properties.getProperty("driver");url=properties.getProperty("url");username=properties.getProperty("username");password=properties.getProperty("password");}catch (Exception e){e.printStackTrace();}}public static Connection getConection(){//获取连接方法Connection connection=null;try{Class.forName(Driver);connection = DriverManager.getConnection(url, username, password);}catch (Exception e){e.printStackTrace();}return connection;}//关闭public static void closeALL(ResultSet rs,PreparedStatement ps, Connection conn){if (rs!=null){try {rs.close();}catch (Exception e){e.printStackTrace();}}if (conn!=null){try {conn.close();}catch (Exception e){e.printStackTrace();}}if (ps!=null){try {ps.close();}catch (Exception e){e.printStackTrace();}}}}
注意:(空指针异常Exception in thread “main” java.lang.NullPointerException)
出现空指针异常:
问题所在位置:(图示为正确的✔)
// 加载配置文件中的数据InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("test.properties");
探究:
(使用getResourceAsStream方法,就会在当前Resource类型文件夹下寻找,所以给文件名即可)
|
|
案例①:查询表的创表结构
//案例:查询表的创表结构
package MySQL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class Demo02 {public static void main(String[] args) throws Exception {Connection conection = JDBCUtil.getConection();String sql="show create table students";PreparedStatement preparedStatement =conection.prepareStatement(sql);ResultSet resultSet = preparedStatement.executeQuery();while (resultSet.next()){String name = resultSet.getString("Create Table");System.out.println(name);}JDBCUtil.closeALL(resultSet,preparedStatement,conection);}
}
案例②查stundents表中,id为1001,sex为男的人的名字
package MySQL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;public class Demo02 {public static void main(String[] args) throws Exception {Connection conection = JDBCUtil.getConection();String sql="select * from students where id=? and sex=?";PreparedStatement preparedStatement =conection.prepareStatement(sql);preparedStatement.setString(1,"1001");preparedStatement.setString(2,"1");ResultSet resultSet = preparedStatement.executeQuery();while (resultSet.next()){String name = resultSet.getString("name");System.out.println(name);}JDBCUtil.closeALL(resultSet,preparedStatement,conection);}
}
案例③查询性别为男的学生信息(1为男,0为女)
package MySQL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class Demo02 {public static void main(String[] args) throws Exception {Connection conection = JDBCUtil.getConection();String sql="select * from students where sex=?";PreparedStatement preparedStatement =conection.prepareStatement(sql);preparedStatement.setString(1,"1");ResultSet resultSet = preparedStatement.executeQuery();System.out.println(" id"+" "+" name"+" "+" age"+" "+" sex");//纯属为了观看while (resultSet.next()){String id = resultSet.getString("id");String age = resultSet.getString("age");String name = resultSet.getString("name");String sex = resultSet.getString("sex");System.out.println(id+" "+name+" "+age+" "+sex);}JDBCUtil.closeALL(resultSet,preparedStatement,conection);}
}
|
|
|
|
|
|
上一章-MySQL篇-Day36-case值替换,备份表,稍微了解一下视图,事务
下一章-Maven先导篇-Day38-安装配置maven,Git
|
|
|
|
|
听说长按大拇指👍会发生神奇的事情呢!好像是下面的画面,听说点过的人🧑一个月内就找到了对象的💑💑💑,并且还中了大奖💴$$$,考试直接拿满分💯,颜值突然就提升了😎,虽然对你好像也不需要,是吧,吴彦祖🤵! |
这篇关于一起重新开始学大数据-MySQL篇-Day37-sql(5)-shell操作MySQL,java操作MySQL、附带JDBC的NullPointerException的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!