MYSQL连接jdbc增删改查案例

下面的这个代码是为了避免重复的进行相同的操作

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);
注意:有几个? 就设置几个值