java应用程序修改数据库

配置和BS差不多，附上程序

1、用来传输对象的DTO

package com.yihaodian.pis.dto;
import java.io.Serializable;
public class SiteCategoryDto implements Serializable
{
    private static final long serialVersionUID = -8202969095017554696L;
    /**
     * 分类id
     */
    private Integer id;
    /**
     * 网站id
     */
    private Integer siteId;
    /**
     * 分类名称
     */
    private String categoryName;
    /**
     * 分类产品列表对应的url
     */
    private String categoryUrl;
    /**
     * 父分类id
     */
    private Integer parentCategoryId;
    /**
     * 分类级别
     */
    private Integer categoryLevel;
    /**
     * 商品抓取数量
     */
    private Integer fetchSize;
    
    
    /**
     * 网站名称
     */
    private String siteName;
    
    /**
     * 父亲分类名称
     */
    private String parentName;
    public Integer getId()
    {
        return id;
    }
    public void setId(Integer id)
    {
        this.id = id;
    }
    public Integer getSiteId()
    {
        return siteId;
    }
    public void setSiteId(Integer siteId)
    {
        this.siteId = siteId;
    }
    public String getCategoryName()
    {
        return categoryName;
    }
    public void setCategoryName(String categoryName)
    {
        this.categoryName = categoryName;
    }
    public String getCategoryUrl()
    {
        return categoryUrl;
    }
    public void setCategoryUrl(String categoryUrl)
    {
        this.categoryUrl = categoryUrl;
    }
    public Integer getParentCategoryId()
    {
        return parentCategoryId;
    }
    public void setParentCategoryId(Integer parentCategoryId)
    {
        this.parentCategoryId = parentCategoryId;
    }
    public Integer getCategoryLevel()
    {
        return categoryLevel;
    }
    public void setCategoryLevel(Integer categoryLevel)
    {
        this.categoryLevel = categoryLevel;
    }
    public Integer getFetchSize()
    {
        return fetchSize;
    }
    public void setFetchSize(Integer fetchSize)
    {
        this.fetchSize = fetchSize;
    }
    @Override
    public String toString()
    {
        StringBuilder builder = new StringBuilder();
        builder.append("SiteCategory [id=");
        builder.append(id);
        builder.append(", siteId=");
        builder.append(siteId);
        builder.append(", categoryName=");
        builder.append(categoryName);
        builder.append(", categoryUrl=");
        builder.append(categoryUrl);
        builder.append(", fetchSize=");
        builder.append(fetchSize);
        builder.append(", categoryLevel=");
        builder.append(categoryLevel);
        builder.append(", parentCategoryId=");
        builder.append(parentCategoryId);
        builder.append("]");
        return builder.toString();
    }
    public String getSiteName()
    {
        return siteName;
    }
    public void setSiteName(String siteName)
    {
        this.siteName = siteName;
    }
    public String getParentName()
    {
        return parentName;
    }
    public void setParentName(String parentName)
    {
        this.parentName = parentName;
    }
}

 

2、对数据库操作的DAO

package com.yihaodian.pricehisotry.dao;

import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;

import com.yihaodian.pis.dto.SiteCategoryDto;

/**
 * 畅销商品数据库操作
 */
public class SiteCategoryDao extends SqlMapClientDaoSupport{

    public SiteCategoryDao() {
       
    }

    public int clearSiteCategories(int id) {
        return this.getSqlMapClientTemplate().delete("clearSiteCategories", id);
    }

    /* 
     * 保存分类畅销商品记录
     */
    public void saveSiteCategory(SiteCategoryDto siteCategory) {
        this.getSqlMapClientTemplate().insert("addSiteCategory", siteCategory);
    }
    public Integer getMaxId(){
    	Object count = this.getSqlMapClientTemplate().queryForObject("getMaxId");
    	return Integer.parseInt(count.toString());
    	
    	}
    /** 
     * 添加分类畅销商品记录
     */
    public int addSiteCategory(SiteCategoryDto siteCategory) {
        return (int) ((Long) this.getSqlMapClientTemplate().insert(
                "addSiteCategory", siteCategory)).longValue();
    }
}

 

3、SQLmap对数据库操作的语句

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" >

<sqlMap namespace="bestseller">
    <typeAlias alias="SiteCategoryDto" type="com.yihaodian.pis.dto.SiteCategoryDto" />

    <resultMap class="SiteCategoryDto" id="SiteCategoryResult">
        <result property="id" column="id" javaType="java.lang.Integer"
			jdbcType="SERIAL" />
        <result property="siteId" column="site_id" javaType="java.lang.Integer"
			jdbcType="INTEGER" />
        <result property="categoryName" column="category_name" javaType="java.lang.String"
			jdbcType="VARCHAR" />
        <result property="categoryUrl" column="category_url" javaType="java.lang.String"
			jdbcType="VARCHAR" />
        <result property="parentCategoryId" column="parent_category_id" javaType="java.lang.Integer"
			jdbcType="INTEGER" />
        <result property="categoryLevel" column="category_level" javaType="java.lang.Integer"
			jdbcType="INTEGER" />
        <result property="fetchSize" column="fetch_size" javaType="java.lang.Integer"
			jdbcType="INTEGER" />
    </resultMap>
	
    <insert id="addSiteCategory" parameterClass="SiteCategoryDto">
		INSERT INTO
		pis.site_category(site_id,category_name,category_url,parent_category_id,category_level,fetch_size)
		VALUES
		(#siteId#,#categoryName#,#categoryUrl#,#parentCategoryId#,#categoryLevel#,#fetchSize#)
        <selectKey>
                SELECT lastval();
        </selectKey>
    </insert>
    <select id="countGetAllSiteCategory" resultClass="java.lang.Integer">
		select
		count(*) FROM pis.site_category
    </select>
    <delete id="deleteSiteCategory" parameterClass="java.lang.Integer">
		DELETE FROM pis.site_category
		where id=#id#
    </delete>

    <delete id="clearSiteCategories" parameterClass="java.lang.Integer">
		DELETE FROM pis.site_category
		where site_id = #site_id#
    </delete>
	
    <select id="countGetCategoryBySite" resultClass="java.lang.Integer">
		select
		count(*) FROM pis.site_category where site_id=#siteId#
    </select>
    
    <select id="countGetCategoryBySiteLevel" resultClass="java.lang.Integer">
		select count(*) FROM pis.site_category 
		where site_id=#siteId# and category_level=#categoryLevel#
    </select>
    
    <select id="getCategoryByName" parameterClass="SiteCategoryDto"
		resultMap="SiteCategoryResult">
		SELECT
		id,site_id,category_name,category_url,parent_category_id,category_level,fetch_size
		FROM pis.site_category
		where site_id=#siteId# and category_level=#categoryLevel# and category_name=#categoryName#
    </select>
    <select id="getCategoryByParentId" parameterClass="java.lang.Integer"
		resultMap="SiteCategoryResult">
		SELECT
		id,site_id,category_name,category_url,parent_category_id,category_level,fetch_size
		FROM pis.site_category
		where parent_category_id=#parentCategoryId#
    </select>
    <select id="queryCategoryById" parameterClass="java.lang.Integer"
		resultMap="SiteCategoryResult">
		SELECT
		id,site_id,category_name,category_url,parent_category_id,category_level,fetch_size
		FROM pis.site_category
		where id=#id#
    </select>
    <select id="getMaxId" resultClass="java.lang.Integer">
        select
        max(id) from pis.site_category;
    </select>
</sqlMap>

4、DAO的申明配置

 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
    <!---->
     <bean id="siteCategoryDao" class="com.yihaodian.pricehisotry.dao.SiteCategoryDao">
        <property name="dataSource" ref="dataSource" />
		<property name="sqlMapClient" ref="sqlMapClient" />
    </bean> 
</beans>

5、连接数据库的配置

 

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"
	xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
       http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd
       http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd">
	
	<!-- org.springframework.beans.factory.config.PropertyPlaceholderConfigurer  com.yihaodian.common.util.DecryptPropertyPlaceholderConfigurer -->
	<bean id="propertyConfigurer" 
		class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
		<property name="location">
			<value>classpath:jdbc.properties</value>
		</property>
	</bean>


	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
		destroy-method="close">
		<property name="driverClassName" value="${jdbc.driverClassName}" />
		<property name="url" value="${jdbc.url}" />
		<property name="username" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />
		<property name="maxActive" value="100" />
		<property name="maxIdle" value="30" />
		<property name="maxWait" value="1000" />
		<property name="defaultAutoCommit" value="true" />
		<property name="removeAbandoned" value="true" />
		<property name="removeAbandonedTimeout" value="60" />
	</bean>


	<!-- SqlMap setup for iBATIS Database Layer -->
	<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
		<property name="configLocation">
			<value>classpath:sqlmap-config.xml</value>
		</property>
		<property name="dataSource" ref="dataSource" />
	</bean>




	<!-- Transaction manager for a single JDBC DataSource -->
	<!-- 
	<tx:annotation-driven />
	<bean id="transactionManager"
		class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="dataSource" />
	</bean>
	 -->

</beans>

6、将数据库操作XML引入

 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
        "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">

<sqlMapConfig>
	<settings enhancementEnabled="true" maxTransactions="10" maxRequests="32" maxSessions="20" />
	<!-- -->
	<sqlMap resource="sqlmap/sqlmap_Bestseller.xml"/> 
</sqlMapConfig>