下面的这个代码是为了避免重复的进行相同的操作
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class JDBCUtils {
private static Properties pro =null;
static {
InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream(“db.properties”);
//定义properties对象,把读取出来的内容放在properties里面
pro =new Properties();
try {
pro.load(in);
Class.forName(pro.getProperty("driverName"));
}catch(Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(pro.getProperty("url"), pro.getProperty("user"), pro.getProperty("password"));
}
}
/**
- 进行数据库的增删改查操作
*/
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
public class tb_student_info_test {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
/**
-
查询
-
@throws SQLException
*/
@Test
public void query() throws SQLException {
try {
conn = JDBCUtils.getConnection();
String sql = “select * from tb_film_info where f_name=?”;
ps = conn.prepareStatement(sql);ps.setString(1, "神话"); System.out.println(sql); rs = ps.executeQuery(); int columnCount = rs.getMetaData().getColumnCount(); System.out.println("总共有:" + columnCount); while (rs.next()) { for (int i = 1; i <= columnCount; i++) { System.out.print(rs.getString(i)); if (i < columnCount) { System.out.print(","); } } System.out.println(); } } catch (Exception e) { e.printStackTrace(); } finally { if (rs != null) { rs.close(); } if (ps != null) { ps.close(); } if (conn != null) { conn.close(); } }
}
/**-
添加
-
@throws SQLException
*/
@Test
public void insertNews() throws SQLException {
try {
conn = JDBCUtils.getConnection();
String sql=“insert into tb_film_info(f_name,f_type,f_score,f_play_year,f_origin) values(?,?,?,?,?)”;
ps=conn.prepareStatement(sql);
ps.setString(1, “动物世界”);
ps.setString(2, “动作片”);
ps.setInt(3, 100);
ps.setInt(4, 2019);
ps.setString(5, “中国”);
int i = ps.executeUpdate();
System.out.println(“成功添加”+i+“条数据”);
}catch(Exception e) {
e.printStackTrace();}finally {
if(rs!=null) {rs.close();}
if(ps!=null) {ps.close();}
if(conn!=null) {conn.close();}
}
}
/** -
修改
-
@throws SQLException
*/
@Test
public void updateNews() throws SQLException {
try {
conn=JDBCUtils.getConnection();
String sql=“update tb_film_info set f_score=? where f_name=?”;
ps=conn.prepareStatement(sql);
ps.setInt(1, 98);
ps.setString(2, “动物世界”);
int i = ps.executeUpdate();
System.out.println(“成功修改”+i+“条数据”);}
catch(Exception e) {
e.printStackTrace();
}finally {
if(rs!=null) {
rs.close();
}
if(ps!=null) {
ps.close();
}
if(conn!=null) {
conn.close();
}}
}
/** -
删除
*/
@Test
public void deleteNews() throws SQLException {
try {
conn=JDBCUtils.getConnection();
String sql=“delete from tb_film_info where f_name=?”;
ps=conn.prepareStatement(sql);ps.setString(1, “动物世界”);
int i = ps.executeUpdate();
System.out.println(“成功删除”+i+“条数据”);}
catch(Exception e) {
e.printStackTrace();
}finally {
if(rs!=null) {
rs.close();
}
if(ps!=null) {
ps.close();
}
if(conn!=null) {
conn.close();
}}
}
-
}
总结:
获取元数据(列)
ResultSet:getMetaData(); 得到元数据;返回的是:ResultSetMetaData对象
getColumnCount();返回有几个列
PreparedStatement
他是Statement接口的子接口
强大之处
防止sql攻击
提高代码的可维护性
提高效率
PreparedStatement 用法
1.先给出sql模板
参数:都用 ?代替
2.调用Connection 的preparedStatement(sql模板);
返回的PreparedStatement;
3.调用setXXX()
setInt(index,value);
setString(index,value);
注意:有几个? 就设置几个值